Question

6–32. (Calculating the components of an annuity payment) You have just bought a house inom do for €270,000 by taking a 20-year mortgage for the same amount at 8 percent per an- num payable in monthly installments. What will your monthly payments be? Use a spreadsheet to calculate your answer. Now calculate the amounts in the 50th monthly payment that goes toward interest and principal, respectively.

Answer 1

Here's the monthly annuity payment schedule:.

Monthly payment = $ 2,258.39 (rounded off to 2 decimal places)

Total interest paid up to 50 months = $ 85,823.87

Total Principal paid up to 50 months = $ 27,095.50

Description	Details	Reference
Loan Amount	$   270,000.00	B2
Interest Rate	8%	B3
Year	20	B4
No. of months per year	12	B5
	<month>		PMT(B3/B5,B4*B5,B2)	IPMT(B3/B5,<month>,B4*B5,B2)	PPMT(B3/B5,<month>,B4*B5,B2)
	Month	Amount at beginning of the month	Total Payment	Interest amount	Principal repaid	Balance at end of the month
	1	$270,000.00	$2,258.39	$1,800.00	$458.39	$269,541.61
	2	$269,541.61	$2,258.39	$1,796.94	$461.44	$269,080.17
	3	$269,080.17	$2,258.39	$1,793.87	$464.52	$268,615.65
	4	$268,615.65	$2,258.39	$1,790.77	$467.62	$268,148.03
	5	$268,148.03	$2,258.39	$1,787.65	$470.73	$267,677.30
	6	$267,677.30	$2,258.39	$1,784.52	$473.87	$267,203.43
	7	$267,203.43	$2,258.39	$1,781.36	$477.03	$266,726.40
	8	$266,726.40	$2,258.39	$1,778.18	$480.21	$266,246.19
	9	$266,246.19	$2,258.39	$1,774.97	$483.41	$265,762.78
	10	$265,762.78	$2,258.39	$1,771.75	$486.64	$265,276.14
	11	$265,276.14	$2,258.39	$1,768.51	$489.88	$264,786.26
	12	$264,786.26	$2,258.39	$1,765.24	$493.15	$264,293.11
	13	$264,293.11	$2,258.39	$1,761.95	$496.43	$263,796.68
	14	$263,796.68	$2,258.39	$1,758.64	$499.74	$263,296.94
	15	$263,296.94	$2,258.39	$1,755.31	$503.08	$262,793.86
	16	$262,793.86	$2,258.39	$1,751.96	$506.43	$262,287.43
	17	$262,287.43	$2,258.39	$1,748.58	$509.81	$261,777.62
	18	$261,777.62	$2,258.39	$1,745.18	$513.20	$261,264.42
	19	$261,264.42	$2,258.39	$1,741.76	$516.63	$260,747.79
	20	$260,747.79	$2,258.39	$1,738.32	$520.07	$260,227.72
	21	$260,227.72	$2,258.39	$1,734.85	$523.54	$259,704.18
	22	$259,704.18	$2,258.39	$1,731.36	$527.03	$259,177.15
	23	$259,177.15	$2,258.39	$1,727.85	$530.54	$258,646.61
	24	$258,646.61	$2,258.39	$1,724.31	$534.08	$258,112.53
	25	$258,112.53	$2,258.39	$1,720.75	$537.64	$257,574.89
	26	$257,574.89	$2,258.39	$1,717.17	$541.22	$257,033.67
	27	$257,033.67	$2,258.39	$1,713.56	$544.83	$256,488.84
	28	$256,488.84	$2,258.39	$1,709.93	$548.46	$255,940.38
	29	$255,940.38	$2,258.39	$1,706.27	$552.12	$255,388.26
	30	$255,388.26	$2,258.39	$1,702.59	$555.80	$254,832.46
	31	$254,832.46	$2,258.39	$1,698.88	$559.51	$254,272.95
	32	$254,272.95	$2,258.39	$1,695.15	$563.24	$253,709.71
	33	$253,709.71	$2,258.39	$1,691.40	$566.99	$253,142.72
	34	$253,142.72	$2,258.39	$1,687.62	$570.77	$252,571.95
	35	$252,571.95	$2,258.39	$1,683.81	$574.58	$251,997.37
	36	$251,997.37	$2,258.39	$1,679.98	$578.41	$251,418.96
	37	$251,418.96	$2,258.39	$1,676.13	$582.26	$250,836.70
	38	$250,836.70	$2,258.39	$1,672.24	$586.14	$250,250.56
	39	$250,250.56	$2,258.39	$1,668.34	$590.05	$249,660.51
	40	$249,660.51	$2,258.39	$1,664.40	$593.98	$249,066.53
	41	$249,066.53	$2,258.39	$1,660.44	$597.94	$248,468.59
	42	$248,468.59	$2,258.39	$1,656.46	$601.93	$247,866.66
	43	$247,866.66	$2,258.39	$1,652.44	$605.94	$247,260.72
	44	$247,260.72	$2,258.39	$1,648.40	$609.98	$246,650.74
	45	$246,650.74	$2,258.39	$1,644.34	$614.05	$246,036.69
	46	$246,036.69	$2,258.39	$1,640.24	$618.14	$245,418.55
	47	$245,418.55	$2,258.39	$1,636.12	$622.26	$244,796.29
	48	$244,796.29	$2,258.39	$1,631.98	$626.41	$244,169.88
	49	$244,169.88	$2,258.39	$1,627.80	$630.59	$243,539.29
	50	$243,539.29	$2,258.39	$1,623.60	$634.79	$242,904.50
	Total up to 50 months			$85,823.87	$ 27.095.50

Excel formulae to compute interest and principal are given in the top. Use the same by prefix "=-" symbol in excel. the same schedule can be continued till 240 months to see "total value" become "$0" at end of 240 months (i.e.) 20 years.