How to Calculate Annuities Using Excel
by C. Taylor
1. Enter the interest rate in decimal format in cell A1. If you are calculating monthly payments, rather than annual payments, enter "=rate/12" and replace "rate" with the actual rate, such as "=0.06/12". If this is the variable you wish to calculate, omit this step.
2. Enter the number of payment periods in cell A2. If you are calculating monthly payments, multiply the number of years by 12 using the formula "=years*12". Replace "years" with the actual number of years, such as "=5*12" for 5 years. If this is the variable you wish to calculate, omit this step.
3. Enter the loan or investment amount in cell A3. If you are entering an investment amount, type it as a negative number, because it represents money you are currently spending. If this is the variable you wish to calculate, omit this step.
4. Enter the
periodic payments in cell A4. If this is the variable you wish to calculate, omit this step.
5. Type "=PMT(A1,A2,A3)" in cell A5 to calculate the periodic payment amount. If the result appears red and parenthesized, it represents a payment you make. If it's in normal font, then it's a payment you receive.
6. Type "=NPER(A1,A4,A3)" in cell A6 to calculate the number of periodic payments.
7. Type "=PV(A1,A2,A4)" in cell A7 to calculate the original loan or investment amount. A loan appears in normal font, because it represents money you received. An investment appears red and parenthesized, because it represent money you initially spent.
8. Type "=RATE(A2,A4,A3)" in cell A8 to calculate the periodic interest rate of the annuity. If you are using monthly periods, rather than annual periods, you may enter "=RATE(A2,A4,A3)*12" to calculate the annual interest rate.
- Frick CPA; Time Value of Money Concepts; David R Frick; Feb 10, 2008