Real Estate Finances on Excel

“Quiz”– Module 3A


Total: 30 points. No time limit. Counts as participation assessment. Show all work for full credit(use formulae for any calculated cell). Submit one Excel file, start each new problem in a new tab (though you may use more than one tab per question)

5 points for clarity of presentation

[10 pts]The average life ? of a bond is defined as

            where PCFt is the principal cash flow at time t, and T is the maturity of the bond.

[1pt.] What is the average life of a zero-coupon bond? Show work

[1pt.] What is the average life of an interest only bond? Show work

[1pt] Consider a fully amortizing level-payment fixed-rate mortgage that does not default, nor is it ever curtailed or prepaid. Show that

where c is the mortgage contract rate.

[7 pts] Compute and graph the average life of a 30 yr. FA FRM in the range of note rates from 2% to 30%. Assume the loan is never prepaid, nor curtailed, nor defaults. Show all work.

[5 pts] A borrower is faced with choosing between two fully amortizing level-payment loans. Loan A is available for $75,000 at 10% MEY for 30 years, with 6 points included in the closing costs. Loan B would be made for the same amount but at 11% MEY for 30 years, with 2 points included in the closing costs. Neither loan defaults/is curtailed.
[4] If the loan is to be repaid after 15 years, which is the better choice?
[2] If the loan is repaid after 5 years, which is the better choice?

Hint: Use the effective cost of borrowing to make the decision. Show work.

[10 pts] A homeowner purchases a property for $1,000,000. She finances the purchase with an 80% LTV, 30-year fully amortizing graduated payment mortgage (GPM) carrying a 10% interest rate. A 20% rate of graduation will be applied to monthly payments beginning year 3 and the beginning of year 5, only (so, fixed for two two-year periods and then fixed for all years 5, 6, 7,…). She will sell the home in year 6. What is the ECB for the loan if the fees are 3% plus $10,000?

Calculate Price

Price (USD)