Excel OfficeExcel Financial Functions perform many of the common financial calculations, such as the calculation of yield, interest rates, duration, valuation and depreciation.
To calculate the periodic interest rate for a loan, given the loan amount, the number of payment periods, and the payment amount, you can use the RATE function. Formula =RATE(periods,payment,-amount)*12 Explanation In the example shown, the formula in C10 is: =RATE(C7,C6,-C5)*12 Loans have four primary components: the amount, the interest rate, the number of periodic… read more »
To calculate the number of periods needed for an annuity to reach a given future value, you can use the NPER function. Formula =NPER(rate,pmt,pv,fv,type) Explanation In the example shown C9 contains this formula: =NPER(C6,-C7,-C4,C5,0) Explanation An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is to… read more »
To calculate the number of payment periods for a loan, given the loan amount, the interest rate, and a periodic payment amount, you can use the NPER function. Formula =NPER(rate,payment,-loan) Explanation In the example shown, the formula in C10 is… =NPER(C6/12,C7,-C5) How this formula works Loans have four primary components: the amount, the interest rate,… read more »
Learn all about Excel’s PMT, RATE, NPER, PV and FV To illustrate Excel’s most popular financial functions, we consider a loan with monthly payments, an annual interest rate of 6%, a 20-year duration, a present value of $150,000 (amount borrowed) and a future value of 0 (that’s what you hope to achieve when you pay off a loan). We make monthly payments,… read more »