The purpose of this homework is to develop your own spreadsheet on constant payment mortgage (the most popular type of fixed rate mortgage) that can calculate its effective interest rate to a borrower (both before and after income-tax), and with an arbitrary prepayment before the loan maturity. Note that when you set prepayment period to be the same as the maximum loan period, it is equivalent to the case of no prepayment. For example, suppose you have a 30 year loan, and you set prepayment period input in the template as 360. In this case, it is equivalent to the case that you hold the loan without prepayment1. This way, the spreadsheet is general enough to give you the effective interest rate with (and without) prepayment. The final spreadsheet shall be self-adaptive, which means when you change the inputs, it shall calculate and report the correct outputs automatically.

Input Loan Amount Contractual Rate Payments per Year Loan Term (Years) Ballon Ending Balance Fees/Discount Points Prepayment Penalty Marginal Income Tax Rate Prepay at the end of period $365,000.00 3.125% 12 15 up to 30 in this template $0.00 c fixed as 0 in this template 2 (1 point 1%) 0% applicable on OSB upon prepayment 20.00%