Question 323671: as a financial planner a client comes to you for investment advice after meeting with him and understanding his needs you offer him the following options option1 invest $5000 in a savings account at 6.6% interest compounded monthly option2 invest into an ordinary annunity where 1,100 is deposited each year into an account that earns 4.8% interest compounded annually. set up the formula for compound interest for option 1 and the formula for future value of an annuity for option2 in an excel spreadsheet to calculate the amount earned at the end of 6 years
Answer by Theo(13342)   (Show Source):
You can put this solution on YOUR website! Option 1 is:
Invest $5000 in a savings account at 6.6% interest compounded monthly.
Option 2 is:
Invest $1100 each year into an annuity that earns 4.8% interest compounded annually.
What is the amount earned with each option after 6 years.
Option 1 will give you $7,421.29 at the end of 6 years which earns you $2,421.29 in interest.
That's the Future Value of $7,421.29 minus the Present Amount of $5,000.00.
Option 2 will give you $7,444.55 at the end of 6 years if you invest at the end of each time period.
Your interest earned is $7,444.55 - total payments of $6600 = $844.548084 = $844.55
Option 2 will give you $7,801.89 at the end of 6 years if you invest at the beginning of each time period.
Your interest earned is $7,801.89 - total payments of $6600 = $1201.886392 = $1201.89
The formula for the Future Value of a Present Amount is given as:
FV = PA * (1+i)^n
FV = Future Value
PA = Present Amount
i = interest rate per time period
n = number of time periods.
Since you are compounding monthly, then the number of time periods = 12 * 6 = 72 and your interest rate per time period = 6.6 / 12 / 100% = .0055
Your numbers become:
FV = what you are trying to find.
PA = 5000
i = .00555
n = 72
Your formula becomes:
FV = 5000 * (1.0055)^72 = $7,421.289944 = $7,421.29
Your annuity formula is a little different.
Both end of time period payment formula and beginning of time period formula are shown below:
FUTURE VALUE OF A PAYMENT WHEN THE PAYMENT IS MADE AT THE END OF EACH TIME PERIOD.
FUTURE VALUE OF A PAYMENT WHEN THE PAYMENT IS MADE AT THE BEGINNING OF EACH TIME PERIOD.
The difference between the two is that extra *(1+i) at the end of the formula for beginning of time period payments.
that represents the extra interest earned when you invest payments at the beginning of each time period rather than at the end of each time period.
FV = future value
PMT = payment per time period
i = interest rate per time period
n = number of time periods
In your problem:
FV = what you want to find.
PMT = 1100
i = interest rate per time period = 4.8% / 100% = .048
n = number of time periods = 6
This is because the time periods for this problem are specified in years so time periods and years are the same which means no adjustment is necessary.
Since you have all the piece parts, all you need to do is plug them into the formula.
Your formula for end of time period payments is shown below:
PMT equals 1100
i = .048
n = 6
The formula becomes:
which becomes:
FV = $7,444.548085 = $7,444.55 with payments at the end of each time period.
For investing of payments at the beginning of each time period, you take that number and multiply it by 1.048 to get:
FV = $7,444.548085 * 1.048 = $7,801.886392 = $7,801.89 with payments at the beginning of each time period.
The monthly cash flows for the Future Value of a Present Amount are shown below:
AIR = Annual Interest Rate
NYRS = Number of Years
CPPY = Number of Compounding Periods per Year
CPIR = Compounding Period Interest Rate = AIR / CPPY
NTP = Number of Time Periods = NYRS * CPPY
TP = Time Point
FV = Future Value. At TP0 this is equal to present Amount.
FV for each time Point is equal to FV for the time point before it * 1.0055.
AIR NYRS CPPY CPIR NTP
0.066 6 12 0.0055 72
TP FV
0 $5,000.00
1 $5,027.50
2 $5,055.15
3 $5,082.95
4 $5,110.91
5 $5,139.02
6 $5,167.29
7 $5,195.71
8 $5,224.28
9 $5,253.02
10 $5,281.91
11 $5,310.96
12 $5,340.17
13 $5,369.54
14 $5,399.07
15 $5,428.77
16 $5,458.62
17 $5,488.65
18 $5,518.83
19 $5,549.19
20 $5,579.71
21 $5,610.40
22 $5,641.25
23 $5,672.28
24 $5,703.48
25 $5,734.85
26 $5,766.39
27 $5,798.10
28 $5,829.99
29 $5,862.06
30 $5,894.30
31 $5,926.72
32 $5,959.32
33 $5,992.09
34 $6,025.05
35 $6,058.19
36 $6,091.51
37 $6,125.01
38 $6,158.70
39 $6,192.57
40 $6,226.63
41 $6,260.88
42 $6,295.31
43 $6,329.93
44 $6,364.75
45 $6,399.76
46 $6,434.95
47 $6,470.35
48 $6,505.93
49 $6,541.72
50 $6,577.70
51 $6,613.87
52 $6,650.25
53 $6,686.83
54 $6,723.60
55 $6,760.58
56 $6,797.77
57 $6,835.15
58 $6,872.75
59 $6,910.55
60 $6,948.56
61 $6,986.77
62 $7,025.20
63 $7,063.84
64 $7,102.69
65 $7,141.75
66 $7,181.03
67 $7,220.53
68 $7,260.24
69 $7,300.17
70 $7,340.32
71 $7,380.70
72 $7,421.29
The yearly cash flows for the End of Year Payments are shown below.
AIR = Annual Interest Rate
CPPY = Number of Compounding Periods per Year
CPIR = Compounding Period Interest Rate = AIR / CPPY
NYRS = Number of Years
NTP = Number of Time Periods = NYRS * CPPY
PMT = Payment per End of each Time Period
TP = Time Point
FV = Future Value.
PMT = Payment at the end of each Time Period
TOTAL = FV + PMT
FV for each Time Period is equal to TOTAL for the time period before it * 1.048
AIR CPPY CPIR NYRS NTP
0.048 1 0.048 6 6
TP FV PMT TOTAL
0 $0.00 $0.00 $0.00
1 $0.00 $1,100.00 $1,100.00
2 $1,152.80 $1,100.00 $2,252.80
3 $2,360.93 $1,100.00 $3,460.93
4 $3,627.06 $1,100.00 $4,727.06
5 $4,953.96 $1,100.00 $6,053.96
6 $6,344.55 $1,100.00 $7,444.55
The yearly cash flows for the Beginning of Year Payments are shown below.
AIR = Annual Interest Rate
CPPY = Number of Compounding Periods per Year
CPIR = Compounding Period Interest Rate = AIR / CPPY
NYRS = Number of Years
NTP = Number of Time Periods = NYRS * CPPY
PMT = Payment per Beginning of each Time Period
TP = Time Point
FV = Future Value.
PMT = Payment at the beginning of each Time Period
TOTAL = FV + PMT
FV for each Time Period is equal to TOTAL for the time period before it * 1.048
AIR CPPY CPIR NYRS NTP
0.048 1 0.048 6 6
TP FV PMT TOTAL
0 $0.00 $1,100.00 $1,100.00
1 $1,152.80 $1,100.00 $2,252.80
2 $2,360.93 $1,100.00 $3,460.93
3 $3,627.06 $1,100.00 $4,727.06
4 $4,953.96 $1,100.00 $6,053.96
5 $6,344.55 $1,100.00 $7,444.55
6 $7,801.89 $0.00 $7,801.89
The answers to your questions are:
Interest earned on Future Value of Present Amount is:
$7,421.29 minus the Present Amount of $5,000.00 = $2,421.29
The Excel Formula to use is:
=FV(0.0055,72,0,-5000,0)
.0055 is the interest rate per month.
72 is the number of months.
0 is the payment per month.
-5000 is the amount invested.
0 means payment is at the end of the time period.
Since payment equals 0, this formula is taking the future value of the amount invested.
Interest earned on End of Time Period Payments is:
$7,444.55 - total payments of $6600 = $844.548084 = $844.55
The Excel Formula to use is:
=FV(0.048,6,-1100,0,0)
.048 is the interest rate per year.
6 is the number of years.
-1100 is the payment per month.
0 is the amount invested.
0 means payment is at the end of the time period.
Interest earned on Beginning of Time Period Payments is:
$7,801.89 - total payments of $6600 = $1201.886392 = $1201.89
The Excel Formula to use is:
=FV(0.048,6,-1100,0,1)
.048 is the interest rate per year.
6 is the number of years.
-1100 is the payment per month.
0 is the amount invested.
1 means payment is at the beginning of the time period.
|
|
|
