Excel Archives - Page 2 of 3

Excel Financial Functions Excel has many special financial functions. This guide shows how to solve financial problems using financial functions in Excel. Time value of money (TVM) is the idea that money is worth more today than in the future. Time value of money is a foundational concept in finance. Basic Financial Functions The following five functions are the basic Excel financial functions that everyone should know. These are also the five functions of a financial calculator. ConceptExcel FunctionDescriptionPresent ValuePVlump sum at the beginning of a problemFuture valueFVlump sum at the end of a problemPaymentPMTannuity, stream of equal payments per period (+ or -)Number of periodsNPERnumber of periods (months, quarters, half years, years)Interest rateRATEinterest rate per period (annual interest rate/periods per year) Present Value - PV The PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today’s dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate. =PV (rate, nper, pmt, [fv], [type]) rate - The interest rate per period.nper - The total number of payment periods.pmt - The payment made each period.fv - [optional] A cash balance you want to attain after the last payment is made. If omitted, assumed to be zero.type - [optional] When payments are due. 0 = end of period, 1 = beginning of period. Default is 0. Future Value - FV The FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate. =FV (rate, nper, pmt, [pv], [type]) rate - The interest rate per period.nper - The total number of payment periods.pmt - The payment made each period. Must be entered as a negative number.pv - [optional] The present value of future payments. If omitted, assumed to be zero. Must be entered as a negative number.type - [optional] When payments are due. 0 = end of period, 1 = beginning of period. Default is 0. Payment - PMT The PMT function is a financial function that returns the periodic payment for a loan. You can use the NPER function to figure out payments for a loan, given the loan amount, number of periods, and interest rate. =PMT (rate, nper, pv, [fv], [type]) rate - The interest rate for the loan.nper - The total number of payments for the loan.pv - The present value, or total value of all loan payments now.fv - [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 0 (zero).type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0. Number of Periods - NPER The NPER function is a financial function that returns the number of periods for loan or investment. You can use the NPER function to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount. =NPER (rate, pmt, pv, [fv], [type]) rate - The interest rate per period.pmt - The payment made each period.pv - The present value, or total value of all payments now.fv - [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 0.type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0. Interest Rate - RATE The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function calculates by iteration. =RATE (nper, pmt, pv, [fv], [type], [guess]) nper - The total number of payment periods.pmt - The payment made each period.pv - The present value, or total value of all loan payments now.fv - [optional] The future value, or desired cash balance after last payment. Default is 0.type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.guess - [optional] Your guess on the rate. Default is 10%. Using a Financial Calculator The most popular financial calculator the TI BA II Plus Professional (Amazon affiliate link). This is the model that I recommend. The BA II Plus Professional is the upgraded version of the TI BA II Plus (Amazon affiliate link). This calculator is used by many business students and professionals for calculating time value of money problems. The BA II Plus and the BA II Plus Professional are official calculators for the following professional exams: Chartered Financial Analyst Certified Financial Planner Certified Management Accountant Excel Vs. Financial Calculator Excel and the TI BA II Plus are both excellent at calculating TVM problems. They do have slightly different function names. The following table summarizes the differences. DescriptionExcelTI BA II PlusPeriods per YearN/AP/YNumber of PeriodsNPERNInterest RateRATEI/YPresent ValuePVPVPaymentPMTPMTFuture ValueFVFV Time Value of Money Using Excel https://youtu.be/84gAPJEAdBs Time Value of Money Using ExcelDownload Time Value of Money Using BA II Plus https://youtu.be/2SwapukcIZQ Time Value of Money using TI BA II Plus CalculatorDownload Advanced Financial Functions in Excel Excel can also perform several advanced financial functions. The following table summarizes these functions. FunctionMeaningNPVnet present valueIRRinternal rate of returnCUMIPMTcumulative interestCUMPRINCcumulative principalFVSCHEDULEfuture value schedule Net Present Value - NPV The Excel NPV function calculates the Net Present Value of an investment, based on a supplied discount rate, and a series of future payments and income. =NPV (rate, value1, value2,…) rate - is the rate of discount over the length of one period.value1 - value1,value2,… are 1 to 254 payments and income, equally spaced in time and occurring at the end of each period.value2 - 1value1,value2,… are 1 to 254 payments and income, equally spaced in time and occurring at the end of each period. Internal Rate of Return - IRR The Excel IRR function returns the Internal Rate of Return for a supplied series of periodic cash flows (i.e. an initial investment value and a series of net income values). =IRR (values, guess) Values - is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.Guess - is a number that you guess is close to the result of IRR; 0.1 (10 percent) if omitted. Cumulative Interest Payment - CUMIPMT The Excel CUMIPMT function is a financial function that returns the cumulative interest paid on a loan between a start period and an end period. You can use CUMIPMT to calculate and verify the total interest paid on a loan, or the interest paid between any two payment periods. =CUMIPMT (rate, nper, pv, start_period, end_period, type) rate - The interest rate per period.nper - The total number of payments for the loan.pv - The present value, or total value of all payments now.start_period - First payment in calculation.end_period - Last payment in calculation.type - When payments are due. 0 = end of period. 1 = beginning of period. Cumulative Principal - CUMPRINC The Excel CUMPRINC function is a financial function that returns the cumulative principal paid on a loan between a start period and an end period. You can use CUMPRINC to calculate and verify the total principal paid on a loan, or the principal paid between any two payment periods. =CUMPRINC (rate, nper, pv, start_period, end_period, type) rate - The interest rate per period.nper - The total number of payments for the loan.pv - The present value, or total value of all payments now.start_period - First payment in calculation.end_period - Last payment in calculation.type - When payments are due. 0 = end of period. 1 = beginning of period. Future Value Schedule - FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates. =FVSCHEDULE(principal,schedule) Principal - is the present value.Schedule - is an array of interest rates to apply. Video: Top 15 Financial Functions in Excel https://youtu.be/4110DWVdC8o