Determine the Future Value of an Annuity

Question

You have instructed your employer to deduct $100 from your paycheque at the end of every

month and automatically invest the money at an annual interest rate of 5 percent compounded

annually. You intend to use this money for your retirement in 20 years. How much will be in the

account at that time?

I know how to solve this problem using finance calculator (TI BA II Plus), but I want to know how to do this with formula?

What if the payments were happened at the beginning of each month?

Answer 1

There are two types of annuity namely ordinary annuity and annuity due.

Ordinary annuity is made at the end of each period.

Annuity due is made at the beginning and not at the end.

FV Ordinary Annuity​=C×[i(1+i)n−1​]

Where :C=cash flow per period

i=interest rate

n=number of payments​

FV ordinary annuity = $100 * [ (1 + 0.05)^20 /0.05

FV annuity due =FV Annuity Due​​=C×[i(1+i)n−1​]×(1+i)​

FV annuity due = $100 * [ (1 + 0.05)^20 – 1 /0.05 * (1 + 0.05)

Answer 2

As you know, the answer is $41,663.10. I solved this problem in Microsoft excel, but it can be solved in any spreadsheet using the following steps:

In an spreadsheet - create five field headings :

1. Months (1 - 241. Note month 241 is final interest payment on month 240);
2. Monthly deduction (=$100 for all records);
3. Running Monthly Principal (this is a running total of your principal contributions (should total $24,000);
4. Annual interest calculation - For all records = Prior month's balance ("Balance" field) multiplied by 5%. (You'll calculate every 12th month. All other records = $0.Note: interest is calculating beginning in month 13, 25, 37, etc. as you stated payments are made annually at beginning of each year).
5. Balance (for the first record, this will = $100). For all other records, this = running total of prior month's balance + interest calculation.

Using excel's "FV" formula, you can prove this. The inputs are "FV(5%/1,20,1200, ,1" = $41,663.10