Modeling Prepayment Penalties in Excel?

Question

Hi All

Hope you're staying healthy and safe. Was wondering if anyone knew how to model out the prepayment penalties lenders have on MF properties? Yield Maintenance and Stepdown. The spreadsheet I'm using doesn't take these into account, so I want to try to build this out. Any tips or insights?

Much appreciated.

James Storey · Answer

@Snehann Kapnadak yield maintenance is a very complex calculation looking at a particular point in time based on current treasuries. I have my own excel model on these in which I can pull the last three days of treasuries and I use for determining proceeds from selling an investment, refinancing, or doing an asset swap to mitigate the yield maintenance fee. I perform this analysis for my clients all the time.

From a high level, the yield maintenance calculation is ((Present Value of Remaining Payments + Present Value of Balloon Payment) - Principal Payoff Balance). The main calculation is easy to understand but how to get the PV of the loan is the challenge because you have to determine the discount rate to use which is basically the interpolated treasury yield of the Weighted Average life of the loan (including balloon payments and weight average principal payments). The more challenging part is trying to determine the discount rate on an average life that isn't directly correlated with an easily accessible treasury. For Example, if the average life of the loan is 12.5 years, you would have to make another fancy calculation to determine the rate on a yield that is between 10 years and 20 years. There are no 12.5 year treasury bonds available.

Please see this article to help determine the average life of a loan or bond.

https://www.investopedia.com/t...

In the end, if you believe treasuries are going to go up, then the yield maintenance can have a major value add aspect. I had a client sell two large multi family properties on a 1031 and the bank actually payed them to pay off the loan all because it was how the loan would "maintain its yield" since they could make more money on the current rates. Make sure to negotiate that from the beginning. On the other hand, if you think rates are going to go down, the yield maintenance might be too big of a risk and you would want to consider doing the step down prepayment penalty structure.

I think I might right an article about this calculation in depth but I need to get motivated to do it first. Hope this helped at least a little bit.

James Storey, CCIM

Greg Dickerson · Answer

You can google that one for tutorials but there is really no Need to model it as It's a simple calculation of the loan balance at time of sale and the percentage of penalty. Step down is simply a reduction of the percentage after time. You would need to know when you sell to calculate so its a moving target and very simple prorated calculation.

Snehann Kapnadak · Answer

@David M.Account Closed

Thanks all for the replies! Basically I'm trying to model out the prepayment costs a the time of ReFI with an IF-THEN statement. I'll have the ReFi loan amount. So IF the prepayment penalty is based on Yield Maintenance, then the Total Prepayment Penalty = the Loan Amount x the [5%-4%-3%-2%-1%] of the year I ReFi? So 5% if I ReFi in Year 1, 4% in Year 2, etc.

Do I have that correct? I know it's hard to visualize on this forum. I appreciate the help.

Account Closed · Answer

@Snehann Kapnadak not knowing anything about your model (other than it's in Excel), its difficult to provide specific, formulaic-type guidance. That said, we classify pre-pay penalties as closing costs. So wherever you are projecting the cash-out refi event in your model, make sure the estimated pre-pay is included in the projected closing costs.

David M. · Answer

@Snehann Kapnadak

I don't fully understand how to calc Yield Maintenance.  A quick Google gives me a basic understanding.

I know this is a text forum, but can you describe exactly, or more closely, what you would like Excel to do for you?

From your response, can you just build a fixed table?  Put the yield maintaince value or year at the top.  Put your refi/loan amount in the first column.  Put your term in the next column.  Then, you have a formula multiplying the absolute location fo the refi/loan amount and the term minus the 'yield maintenance year' at the top.  now you have a table showing your prepayment penalty by year.

If you don't want a table and just want a single value, you can leave one more column for your prepayment year.  Just keep changing that one cell.

This eliminates a computationally intensive and messy nested if-then statement that doesn't scale.  Honestly, I'm still not picturing what are you doing with the if-then statement.

Ryan Hogan · Answer

I see where you're going with YM vs SD... one way is Data Validation list with YM or SD, then into a nested if/then function, but within the nested function, you may need a max of say two criteria from your lender for YM.  How granular you want to get, for example if your original plan is to hold for 60 months, but you may decide in month 34 you want to ReFi, pending your lender, they may have two criteria, either a Minimum Prepayment Penalty (e.g. 1% of the loan) or an Equivalent Yield of Remaining Term, the Maximum of the two.  Example formula:

=MAX(Minimum_Prepayment_Penalty*Outstanding_Loan_Balance_Upon_Payoff,((1-((1+Equivalent_Yield_of_Remaining_Term_Treasury)^(-Remaining_Term_Upon_Payoff/12)))/Equivalent_Yield_of_Remaining_Term_Treasury)*((Outstanding_Loan_Balance_Upon_Payoff*(Interest_Rate-Equivalent_Yield_of_Remaining_Term_Treasury))))

I don't have anything for SD, yet.  A work in progress...

Catherine Javier · Answer

Hi, Newbie here. Is this a DSCR loan?  I want to ask your opinion on this. I have the same prepayment Penalty of 5,4, 3, 2, 1. Any idea if I refinance the loan, can the prepayment penalty percentage be written off or can be deducted in tax?

Thank you so much

Snehann Kapnadak · Answer

@David M. Ah okay, I get you now. Thanks for the tip!

David M. · Answer

@Snehann Kapnadak

I think you'd be using a VLOOKUP function to find the value off the table...

My point is that you can eliminate the "exterior" table all the together and be able to calc the numbers directly in one cell alone.  Of course, that elminates the extra table, but it would be "cleaner" assuming you didn't want to see the table of info

Snehann Kapnadak · Answer

@David M. True, although I think I'd still need an IF-Then, to pull the exact value of the penalty depending on the year of disposition.

Modeling Prepayment Penalties in Excel?

Most Popular Reply