For the spreadsheet below please use excel to replace the numbers in the cells from the Loan and Borrower Cost Information cell to the Incremental Cost of Funds cell, with equations that reference the top section (Incremental cost of funds cell to Mortgage interest rate cell).
To be clear, you must use equations to recreate the bottom two sections of the spreadsheet from the numbers in the first section of the spreadsheet.
Show these equations and make sure they equal the numbers provided in the bottom two sections.
| Incremental Cost of Funds |
| Assumptions |
Base Case |
|
|
| Loan Amortization Term |
30 |
30 |
30 |
| Prepayment Year |
10 |
10 |
10 |
| Property Value |
$200,000 |
$200,000 |
$200,000 |
| LTVR |
80.00% |
85.00% |
90.00% |
| Points |
0.00% |
0.25% |
0.50% |
| Incremental Closing Costs |
0 |
750 |
1,500 |
| UST10 |
2.20% |
2.20% |
2.20% |
| Spread |
1.70% |
2.00% |
2.30% |
| Mortgage Interest Rate |
3.90% |
4.20% |
4.50% |
|
|
|
|
| Loan and Borrower Cost Information |
|
|
|
| Loan Amount |
160,000 |
170,000 |
180,000 |
| Points and Increment Costs |
0 |
1,175 |
2,400 |
| Funds Disbursed |
160,000 |
168,825 |
177,600 |
| Debt Service |
755 |
831 |
912 |
| Outstanding Loan Balance |
125,627 |
134,831 |
144,161 |
| Borrower Cost of Funds |
3.90% |
4.29% |
4.68% |
|
|
|
|
| Incremental Cost of Funds (relative to base case) |
|
|
| Incremental New Funds |
|
8,825 |
17,600 |
| Incremental Debt Service |
|
77 |
157 |
| Incremental Outstanding Loan Balance |
|
9,205 |
18,534 |
| Incremental Cost of Funds |
|
10.67% |
11.02% |