Question

Question #1 Create a spreadsheet file to calculate the future value of an investment account which: a) starts out with $24,350 b) receives payments of $1,500 per year for the first 10 years, and then $2,400 per year for the next 15 years, but no payments at all for the last 5 years c) earns 8.75% interest.

Question #2 What is the total future value of this account at the end of the 30 years?

Question #3 How much interest does the account earn in year 14?

Question #4 What is the total amount of interest earned over the course of the 30 years?

Answer 1

Since you need an excel file, I will create a dynamic file and then share a snap shot how formulae and linked cells to show how it was created.

Interest rate

8.75%

Year	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
Cash flows	24,350	1,500	1,500	1,500	1,500	1,500	1,500	1,500	1,500	1,500	1,500	2,400	2,400	2,400	2,400	2,400	2,400	2,400	2,400	2,400	2,400	2,400	2,400	2,400	2,400	2,400	-	-	-	-	-
FV of each year's cash flows	301,562.22	17,082.05	$15,707.63	14,443.80	13,281.65	12,213.02	11,230.36	10,326.77	9,495.88	8,731.84	8,029.28	11,813.19	10,862.71	9,988.70	9,185.01	8,445.98	7,766.42	7,141.54	6,566.93	6,038.56	5,552.70	5,105.93	4,695.11	4,317.34	3,969.97	3,650.54	-	-	-	-	-
Another formula	301,562.22	17,082.05	15,707.63	14,443.80	13,281.65	12,213.02	11,230.36	10,326.77	9,495.88	8,731.84	8,029.28	11,813.19	10,862.71	9,988.70	9,185.01	8,445.98	7,766.42	7,141.54	6,566.93	6,038.56	5,552.70	5,105.93	4,695.11	4,317.34	3,969.97	3,650.54	-	-	-	-	-
Total FV after 30 years	527,205.10
Total value of principal till year 13 end	46,550
Interest over this principal in 14th year	4,073.13
Total value of principal in 30 years	75,350
Total interest earned	451,855.10