Question

Question 6: Chandra is considering borrowing $150,000 to buy a house, using a 15-year fixed-rate mortgage. Assuming quoted annual interest rate (APR) of 3.9%, put together an amortization table showing all of the monthly payments broken down into the amount going toward paying interest and the amount going toward paying down the mortgage loan balance. You should also have the beginning and ending balance for each month in your table. Please make sure you use cell references and copying and pasting to fill in the rest of the table once you enter the necessary formulas for the first and the beginning of the second month.

Answer 1

Month Count	O/S Principal at the beginning of period	Installment Value	Interest Payment	Principal Repayment	O/S principal at the end of period
0	$                              150,000
1	$                              150,000	$                      1,102	$                         488	$                                615	$        149,385
2	$                              149,385	$                      1,102	$                         486	$                                617	$        148,769
3	$                              148,769	$                      1,102	$                         483	$                                619	$        148,150
4	$                              148,150	$                      1,102	$                         481	$                                621	$        147,530
5	$                              147,530	$                      1,102	$                         479	$                                623	$        146,907
6	$                              146,907	$                      1,102	$                         477	$                                625	$        146,283
7	$                              146,283	$                      1,102	$                         475	$                                627	$        145,656
8	$                              145,656	$                      1,102	$                         473	$                                629	$        145,027
9	$                              145,027	$                      1,102	$                         471	$                                631	$        144,397
10	$                              144,397	$                      1,102	$                         469	$                                633	$        143,764
11	$                              143,764	$                      1,102	$                         467	$                                635	$        143,129
12	$                              143,129	$                      1,102	$                         465	$                                637	$        142,492
13	$                              142,492	$                      1,102	$                         463	$                                639	$        141,853
14	$                              141,853	$                      1,102	$                         461	$                                641	$        141,212
15	$                              141,212	$                      1,102	$                         459	$                                643	$        140,569

The first column gives the principal at the start of the month. the monthly installment is calculated using the PMT function in excel. Out of the installment, the interest payment is deducted and then the remaining goes towards principal repayment. the last column gives the outstanding principal at the end of the month