Annuity future value, and solving for the payment

◈ 8 cards

FV of a savings plan with =FV(rate,nper,pmt), then the payment that repays a loan (principal in pv) or reaches a target (target in fv) with =PMT.

Saving the same amount every year

The future value of an ordinary annuity is what a series of equal end-of-period deposits accumulates to at the moment of the last deposit:

$F V = P M T \times \frac{( 1 + r ) ^{n} - 1}{r}$

The factor is the FVAF, and it is always greater than n — the deposits are all there, plus interest — the mirror of Lesson 7.1's PVAF < n. In Excel, =FV(rate, nper, pmt), with the deposit entered negative so the accumulated sum reads positive.

Worked example — Renata's retirement plan

Renata deposits $2,500 at the end of each year for 20 years at 7 %.

$F V A F = \frac{1.0 7 ^{20} - 1}{0.07} = \frac{3.869684 - 1}{0.07} = 40.9955 F V = 2, 500 \times 40.9955 = 102, 488.73$

=FV(0.07,20,-2500) → 102,488.73. She deposited $50,000; the other $52,488.73 is interest. The first deposit compounds for 19 years, the last for none — it goes in on the day the balance is measured. That is why the FV of an ordinary annuity uses $(1 + r)^{n} - 1$ : it is a geometric series whose largest exponent is $n - 1$ .

Solving for the payment: two shapes

=PMT(rate, nper, pv, fv, type) finds the level payment that balances the other flows. Two problems on the paper look alike and are keyed differently.

A loan. Tamarack Foods borrows $25,000 and repays it in 5 equal year-end instalments at 7 %. The principal is a present value: =-PMT(0.07,5,25000) = 6,097.27. By hand, $25{,}000 \div PVAF(7\,\%, 5) = 25{,}000 \div 4.100197$. Five payments total 30,486.35; the excess over 25,000 is interest.

A savings goal. Renata wants $50,000 in 10 years at 5 %. The target is a future value, and there is nothing in the account today: =-PMT(0.05,10,0,50000) = 3,975.23 a year. By hand, $50{,}000 \div FVAF(5\,\%, 10) = 50{,}000 \div 12.577893$.

Swap the two and the payment is absurd: =-PMT(0.07,5,0,25000) = 4,347.27 (a loan keyed as a goal — too small to repay 25,000 with interest), and =-PMT(0.05,10,50000) = 6,475.23 (a goal keyed as a loan — far too large). The loan goes in pv; the target goes in fv with pv = 0.

A monthly loan

Ossington Brewing finances a $18,000 van over 4 years at 6.9 % compounded monthly: =-PMT(0.069/12,4*12,18000) = 430.20 a month. Rate ÷ 12 and years × 12 together; the answer is a monthly payment because the periods are months.