Anonymous# 6 months, 3 weeks ago
One question relates to debt that is already in place when I am updating the model in more of an asset management function. If, for example, I entered into a note at the time of acquisition 12 months ago (hypothetically), could I just enter in the date of funding as month (minus) 12?
What is best practice to achieve this function and have debt properly amortize?Spencer BurtonKeymaster# 6 months, 3 weeks ago
This is a really good question. At some point I’ll put a tutorial together on how to do this, as loan assumptions are fairly common and the model isn’t especially intuitive around this concept.
To enter an existing loan into the model, 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.
Thanks for your contribution to the model!Shafi NossParticipant# 1 month, 2 weeks ago
Is there a way to model passing on existing debt to the next buyer? I’m analyzing a multifamily value add with a loan assumption and I want to look at disposition before it amortizes. I’m not sure how to have that show up in the return analysis.
You must be logged in to reply to this topic.