Contents
This example will show you how to calculate the future value of an investment and the present value of an annuity.
Tip: When working with Excel’s financial functions, always ask yourself the question, am I paying (expense) or am I receiving money (income)?
Investment
Suppose that at the end of each year, you put $100 into a savings account. How much will you receive in 10 years with an annual interest rate of 8%?
- Paste a function FV (Future Value), in the version – BS (Future Value).
=FV(rate,nper,pmt,[pv],[type])=БС(ставка;кпер;плт;[пс];[тип]) - Enter arguments:
=FV(C2,C3,C4)=БС(C2;C3;C4)
For 10 years you deposit 10*100 = $1000 (expense). And at the end of the term, you will receive $1448,66 (income). The larger the amount, the faster your money is multiplied.
Note: The last two arguments are optional. If you omit the argument pv (ps), it will be 0 (no current value). If you omit the argument type (type), it is assumed that payments are due at the end of the period.
annuity
Let’s say you want to buy an annuity that will pay $600 a month for the next 20 years. How much will an annuity with an annual interest rate of 6% cost?
- Paste a function PV (Present Value), in the version – PS (Present Value).
=PV(rate,nper,pmt,[fv],[type])=ПС(ставка;кпер;плт;[бс];[тип]) - Enter arguments:
=PV(C4/12,C5*12,C6)=ПС(C4/12;C5*12;C6)You need to make a one-time payment of $83748,46 (an expense) to pay for this annuity. In the future, you will receive 240 * 600 (income) = $144000. This is another example of how money grows over time.
Note: We receive monthly payments, so we use the formula 6/12 = 0,5% for the tariff and 20 * 12 = 240 for the argument nDue (nper). The last two arguments are optional. If the argument is omitted fv (bs), so it is 0 (no future value). If the argument is omitted type (type), it is assumed that payments are due at the end of the period. This annuity does not take into account life expectancy, inflation, etc.