logo for solving-math-problems.com
leftimage for solving-math-problems.com

Compound Interest: Interest Rate 5.65%, Principal $4,800.00











































Compound Interest
Interest Rate 5.65%
Principal $4,800.00
Rests per Year(n) 365

Time (year) Principal ($) Yearly Interest Total Interest
1 $5,078.99 $278.99 $278.99
2 $5,374.19 $295.20 $574.19
3 $5,686.54 $312.36 $886.54
4 $6,017.06 $330.51 $1,217.06
5 $6,366.78 $349.72 $1,566.78
6 $6,736.83 $370.05 $1,936.83
7 $7,128.39 $391.56 $2,328.39
8 $7,542.71 $414.32 $2,742.71
9 $7,981.10 $438.40 $3,181.10
10 $8,444.98 $463.88 $3,644.98
11 $8,935.82 $490.84 $4,135.82
12 $9,455.19 $519.37 $4,655.19
13 $10,004.74 $549.55 $5,204.74
14 $10,586.24 $581.50 $5,786.24
15 $11,201.53 $615.29 $6,401.53
16 $11,852.58 $651.06 $7,052.58
17 $12,541.48 $688.90 $7,741.48
18 $13,270.42 $728.94 $8,470.42
19 $14,041.72 $771.30 $9,241.72
20 $14,857.85 $816.13 $10,057.85
21 $15,721.42 $863.57 $10,921.42
22 $16,635.18 $913.76 $11,835.18
23 $17,602.05 $966.87 $12,802.05
24 $18,625.12 $1,023.07 $13,825.12
25 $19,707.64 $1,082.53 $14,907.64
26 $20,853.09 $1,145.45 $16,053.09
27 $22,065.12 $1,212.02 $17,265.12
28 $23,347.58 $1,282.47 $18,547.58
29 $24,704.59 $1,357.01 $19,904.59
30 $26,140.47 $1,435.88 $21,340.47




Now the question is:

Place a number other than '1' into A7 (=time:1st year)

a) identify the cell which has an incorrect value in it.
b) what value should be in that cell?
c) write an excel formula that will ensure all values are correct regardless of the starting year.

Comments for Compound Interest: Interest Rate 5.65%, Principal $4,800.00

Click here to add your own comments

Sep 03, 2011
Compound Interest - Excel Formulas
by: Staff

-----------------------------------------------------

Part II

Here is a summary of the original values we used to set up the compound interest formula:

PV = 4800
R = .0565/365
N = years * 365

INV =PV*(1+R)^N

INV =4800*(1+.0565/365)^( years*365)


This is the original Excel spreadsheet set-up:

. . . . . . .Col 1 . . Col 2 . . . .Col 3 . . . . . . Col 4. . . . . .Col 5

Row 1 . years . . days . Principle ($) . . Yearly Int . . Total Int
(titles)

Row 2 . . . 0 . .(blank) . . . . 4800 . . . . . . . .0 . . . . . . . . 0
(values at time zero)

Row 3 . . . 1 . =R3C1*365. . =4800*(1+0.0565/365)^R3C2 . . =R3C3-R2C3 . . =R2C5+R3C4
(excel formulas to compute the values at the end of year 1)

Row 4 . . . 2 . =R4C1*365. . =4800*(1+0.0565/365)^R4C2 . . =R4C3-R3C3 . . =R3C5+R4C4
(excel formulas to compute the values at the end of year 2)

Row 5 . . . 3 . =R5C1*365. . =4800*(1+0.0565/365)^R5C2 . . =R5C3-R4C3 . . =R4C5+R5C4
(excel formulas to compute the values at the end of year 3)

Row 6 . . . 3 . =R6C1*365. . =4800*(1+0.0565/365)^R6C2 . . =R6C3-R5C3 . . =R5C5+R6C4
(excel formulas to compute the values at the end of year 4)

.
.
.

The original excel spread sheet is shown below:

(1) Click the following link to VIEW a snapshot of the excel spreadsheet computations; or (2A) highlight and copy the link, then (2B) paste the link into your browser Address bar & press enter:

Use the Backspace key to return to this page:

http://www.solving-math-problems.com/images/Comp_Int_Table_2011-09-01.png


The original values for row 3 are shown below:


. . . . . . .Col 1 . . Col 2 . . . .Col 3 . . . . . . Col 4. . . . . .Col 5


Row 1 . years . . days . Principle ($) . . Yearly Int . . Total Int
.
.
.
Row 3 . . . 1 . . . . .365 . . .5078.99. . . . . . . . 278.99 . . . .278.99

If the value of R3C1 is changed from a 1 to a 5, all the cells in Row 3 change to the following values:

Row 3 . . . 5 . . . .1825 . . .6366.78. . . . . . . .1566.78. . . .1566.78

And . . .

Two of the cells in row 4 also change


The excel formulas are already written in such a way that the value of the Principle (col 3) is computed independently for each row. The values listed in preceding or subsequent rows do not affect the calculation of the Principle (col3).

The excel formula for the Principle (col 3) is:

=4800*(1+0.0565/365)^RcurrentrowC2


However, if cell 1 is changed, the Yearly Int and Total Int calculations are affected for the row immediately following.


These calculations can be changed to make them independent as follows:

Yearly Interest


=4800*(1+0.0565/365)^(365*( RcurrentrowC1)) -4800*(1+0.0565/365)^(365*( RcurrentrowC1-1))

Total Interest

=4800*(1+0.0565/365)^(365*( RcurrentrowC1))-4800



Thanks for writing.

Staff
www.solving-math-problems.com



Sep 03, 2011
Compound Interest - Excel Formulas
by: Staff


Part I

The question:

Compound Interest
Interest Rate 5.65%
Principal $4,800.00
Rests per Year(n) 365

Time (year) Principal ($) Yearly Interest Total Interest
1 $5,078.99 $278.99 $278.99
2 $5,374.19 $295.20 $574.19
3 $5,686.54 $312.36 $886.54
4 $6,017.06 $330.51 $1,217.06
5 $6,366.78 $349.72 $1,566.78
6 $6,736.83 $370.05 $1,936.83
7 $7,128.39 $391.56 $2,328.39
8 $7,542.71 $414.32 $2,742.71
9 $7,981.10 $438.40 $3,181.10
10 $8,444.98 $463.88 $3,644.98
11 $8,935.82 $490.84 $4,135.82
12 $9,455.19 $519.37 $4,655.19
13 $10,004.74 $549.55 $5,204.74
14 $10,586.24 $581.50 $5,786.24
15 $11,201.53 $615.29 $6,401.53
16 $11,852.58 $651.06 $7,052.58
17 $12,541.48 $688.90 $7,741.48
18 $13,270.42 $728.94 $8,470.42
19 $14,041.72 $771.30 $9,241.72
20 $14,857.85 $816.13 $10,057.85
21 $15,721.42 $863.57 $10,921.42
22 $16,635.18 $913.76 $11,835.18
23 $17,602.05 $966.87 $12,802.05
24 $18,625.12 $1,023.07 $13,825.12
25 $19,707.64 $1,082.53 $14,907.64
26 $20,853.09 $1,145.45 $16,053.09
27 $22,065.12 $1,212.02 $17,265.12
28 $23,347.58 $1,282.47 $18,547.58
29 $24,704.59 $1,357.01 $19,904.59
30 $26,140.47 $1,435.88 $21,340.47




Now the question is:

Place a number other than '1' into A7 (=time:1st year)

a) identify the cell which has an incorrect value in it.
b) what value should be in that cell?
c) write an excel formula that will ensure all values are correct regardless of the starting year.

The answer:

We can use the same excel spread sheet as before.


INV =PV*(1+R)^N

INV = Value of Investment at the end of the nth time period

PV = present value of the investment at time zero
PV = the original investment = $4800

R = interest rate for each time period N
(The interest rate R is COMPOUNDED DAILY for your problem)
R_annual_percent_form = the 5.65%
(5.65% must be converted to a decimal value)
R_annual_decimal_form = 5.65%/100 = .0565
(the annual interest rate in decimal form must be converted to a daily interest rate in decimal form)
R_daily_decimal_form = .0565/365 days (I didn’t divide these two numbers. Instead I put .0565/365 directly into the excel formula)
R_daily_decimal_form = .0565/365

N = time period in days = Time in years * 365 days per year

-----------------------------------------------------

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Math Questions & Comments - 01.



Copyright © 2008-2015. All rights reserved. Solving-Math-Problems.com