To enter an existing loan into the model and have the debt properly amortize, you’ll first need to know the following information:
1) Existing loan balance
2) Original amortization in months (e.g. 360 for a 30-year loan)
3) The monthly amortizing payment amount. Also, the monthly interest-only payment, if the loan still has IO period left
4) If there is IO left, how many months
5) How many months are left on the loan term
Enter the existing loan balance in cell E12, the original amortization in cell E15, the IO remaining in E18, and how many months are left on the loan term in cell E17.
Then, override the ‘Amort. Payments (Monthly) and ‘IO Payments (Monthly) calculations (cells E19 and E20) with the actual amortizing and IO payments.
With the above values entered, and assuming the loan interest is charged on a 30/360 basis, the model will amortize the loan correctly.
With all of that said, there’s one issue I need to fix (I’ll include this in the next release). The Perm. Debt module didn’t originally allow for a mid-hold refinance. When I added the Refinance Debt module, I failed to account for a self-amortizing loan payoff mid-hold. As a result, if the existing loan’s term ends during the analysis period, the ‘Loan Payoff’ row (Perm. Debt row 16) will output an erroneous payoff in the payoff month.
To correct this, simply set the amount in cell E21 to 0.