# Excel problem

Printable View

• Nov 21st 2010, 10:17 AM
terminator
Excel problem
Mr.Love invested money for his retirement as follows:
• starting at age 30 (on his 30th birthday), he invested \$800.00 each year on his birthday up to and including his 54th birthday
• at various times (always on his birthday) he invested additional amounts of money:
o \$2000.00 on his 35th birthday
o \$5000.00 on his 40th birthday
o \$1000.00 on his 43th birthday
o \$6000.00 on his 50th birthday

Mr. Love is now 55 years old (and yes, it is his birthday), and he wants to check up on the calculations of the financial institution where he invested his money. The financial institution reported that the rate of growth had been approximately 19% per year compounded annually.
Prepare an investment spreadsheet for Mr.Love that includes one column for regular payments, and another column for additional payments. The spreadsheet should also show the balance at each birthday, including his 55th birthday. Use a rate of growth (interest rate) as 19% per annum compounded annually.

I have solved the problem(see attached) but I need to explain how I calculated the interest.

=C9*\$D\$4/\$D\$5 (I've got it from the book but doesn't make any sense to me)[/FONT]
• Nov 21st 2010, 10:24 AM
wonderboy1953
Quote:

Originally Posted by terminator
Mr. Love is now 55 years old (and yes, it is his birthday), and he wants to check up on the calculations of the financial institution where he invested his money. The financial institution reported that the rate of growth had been approximately 19% per year compounded annually.
Prepare an investment spreadsheet for Mr.Love that includes one column for regular payments, and another column for additional payments. The spreadsheet should also show the balance at each birthday, including his 55th birthday. Use a rate of growth (interest rate) as 19% per annum compounded annually.

I have solved the problem(see attached) but I need to explain how I calculated the interest.

=C9*\$D\$4/\$D\$5 (I've got it from the book but doesn't make any sense to me)[/FONT]

This one should have been posted under business math.
• Nov 21st 2010, 10:40 PM
CaptainBlack
Quote:

Originally Posted by terminator
Mr.Love invested money for his retirement as follows:
• starting at age 30 (on his 30th birthday), he invested \$800.00 each year on his birthday up to and including his 54th birthday
• at various times (always on his birthday) he invested additional amounts of money:
o \$2000.00 on his 35th birthday
o \$5000.00 on his 40th birthday
o \$1000.00 on his 43th birthday
o \$6000.00 on his 50th birthday

Mr. Love is now 55 years old (and yes, it is his birthday), and he wants to check up on the calculations of the financial institution where he invested his money. The financial institution reported that the rate of growth had been approximately 19% per year compounded annually.
Prepare an investment spreadsheet for Mr.Love that includes one column for regular payments, and another column for additional payments. The spreadsheet should also show the balance at each birthday, including his 55th birthday. Use a rate of growth (interest rate) as 19% per annum compounded annually.

I have solved the problem(see attached) but I need to explain how I calculated the interest.

=C9*\$D\$4/\$D\$5 (I've got it from the book but doesn't make any sense to me)[/FONT]

1. You have calculated the payments incorrectly, he pays in \$800 every year on his birthday.

2. The interest is the closing balance minus the total payments

CB
• Nov 22nd 2010, 10:56 AM
Wilmer
Code:

```AGE  PAYMENT  EXTRA P.    INTEREST      BALANCE  30  800.00      .00          .00        800.00  31  800.00      .00      152.00      1,752.00  -  35  800.00  2,000.00    1,109.08      9,746.36  -  40  800.00  5,000.00    4,517.78    34,095.54  -  43  800.00  1,000.00    9,506.59    61,341.29  -  50  800.00  6,000.00    34,568.78    223,309.75  -  54  800.00      .00    72,047.49    452,044.80  55      .00      .00    85,888.51    537,933.31```
This is what your spreadsheet should give you.
As far as total interest goes, I presume Excel can add the "Interest Column".
If not, then (as CB told you) total interest = 537,933.31 - (All Payments).