Investment or Annuity in Excel

This example teaches you how to calculate the future value of an investment or the present value of an annuity.

Tip: when working with financial functions in Excel, always ask yourself the question, am I making a payment (negative) or am I receiving money (positive)?

Investment

Assume that at the end of every year, you deposit $100 into a savings account. At an annual interest rate of 8%, how much will your investment be worth after 10 years?

1. Insert the FV (Future Value) function.

2. Enter the arguments.

In 10 years time, you pay 10 * $100 (negative) = $1000, and you'll receive $1,448.66 (positive) after 10 years. The higher the interest, the faster your money grows.

Note: the last two arguments are optional. If omitted, Pv = 0 (no present value). If Type is omitted, it is assumed that payments are due at the end of the period.

Annuity

Assume you want to purchase an annuity that will pay $600 a month, for the next 20 years. At an annual interest rate of 6%, how much does the annuity cost?

1. Insert the PV (Present Value) function.

2. Enter the arguments.

You need a one-time payment of $83,748.46 (negative) to pay this annuity. You'll receive 240 * $600 (positive) = $144,000 in the future. This is another example that money grows over time.

Note: we receive monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper. The last two arguments are optional. If omitted, Fv = 0 (no future value). If Type is omitted, it is assumed that payments are due at the end of the period. This annuity does not take into account life expectancy, inflation etc.

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Excel Functions and Data Analysis in Excel.