Thanks for pointing that out. The only thing I can think of as to why the double formula was that the original version of this spreadsheet was created using an earlier version of Excel. Back then, there was no IFERROR function. Errors were handled with the ISERROR function and were written something like =IF(ISERROR(A1+A2),”0”, A1+A2). In other words, the formula had to be written twice. When I updated the spreadsheet in a later version of Excel and converted it to using IFERROR, I must have left part of the old formula in that was not needed.

Thanks again.

Thank you for this spreadsheet!

]]>Sorry, but I do not have any spreadsheets for inherited IRS’s. I put together the one on my blog when I turned 70 1/2 and thought it might help others.

Don ]]>

Thank you

Kelly Jo

On the sheet “Rule of 78”, a $32,000 loan for 24 months at 5.75% would have an $18,560 playoff between month 11 find 12. Is the payoff balance the balloon? If so, an $18,560 payoff would give a refund interpolated to $545.19. ]]>

I am trying to calculate a monthly pay off using rule of 78 on a loan with balloon payment like below: I see the chart up but that assumes zero balloon payment at the end of the loan.

Starting principal $32000.00

Interest Rate 5.75%

Term 24 months

Balloon $18560.00 ]]>

Would you care to share any reading on the types of methods used to calculate IRR on a pool of loans.

Im setting up a leding fund in Peru to service mortgage backed business loans, tickets averaging $100k, we expect to get some 30 deals this first year. Maturities will be in the 2 -4 year range.

I wonder what you would recommend not to do in the model.

Thank you in advance. ]]>

I will email you. ]]>

I wanted to pick your brain on smth. I have a portfolio of residential and commercial loans and I want to see if I have to liquidate this portfolio within the next 30 months, how I would go about that.

For arm loans, I amortized the portfolio in excel using given margins and projected index values from bloomberg (some use 3 mo libor, some 1 month libor and several other indices) Then, I looked at the weighted average age of the portfolio for different asset categories, e.g. jumbo arm loans, helocs, gse conforming loans, etc. The age was over 30 months for each category, so I used 6% cpr and calculated monthly single month mortality based on that. I projected out principal interest and prepayments for 30 months. Then, for each asset category level, I aggregated P&I and prepayments and principal balances. After that, I calculated each, principal, interest and prepayment as a percentage of the beginning principal balance, for each asset category. Then, I came up with a liquidation schedule and decreased the principal balances of asset buckets based on this schedule. At that time, I used the percentages I had calculated previously, to roughly calculate updated p&I and prepayments. Since they were calculated as a % of principal balance, all the numbers were automatically updated when I change the liquidation amounts.

My first question is around what I have done so far: Does it make sense the way I did this in terms of using the projected indices from bloomberg, assumptions for prepayments and using the percentages to calculate post liquidation P&I and prepayment?

Second one is about default rates and loss severities. What kind of assumptions would you recommend using or would it be simpler to ignore defaults considering this liquidation exercise is only projected for 30 months?

I am looking forward to your insights on this and thank you very much for sharing your knowledge and experience with us.

Ian

]]>The same basic question came in within half and hour, but responding to a different post. I assume they are both from the same person.

The answer lies in the sequence of principal reduction. Notice that prepayment reduces principal, after the payment principal is deducted from the starting principal balance. Likewise, principal defaults are reduced after the payment and the prepayment.

Therefore, normal P&I came in before any prepayments, and defaults take place. The prepayments and defaults are used establish the starting balance of the next payment. I hope that helps.

Don

P.S. Also, remember that these amortization schedules are from the standpoint of the investor. Even though defaults may be indicated in a given month, the investor is advanced the total P&I from the servicer. From the investor standpoint , defaults can be thought of as just a prepayment, due to the guaranteed P&I. ]]>

The same basic question came in within half and hour, but responding to a different post. I assume they are both from the same person.

The answer lies in the sequence of principal reduction. Notice that prepayment reduces principal, after the payment principal is deducted from the starting principal balance. Likewise, principal defaults are reduced after the payment and the prepayment.

Therefore, normal P&I came in before any prepayments, and defaults take place. The prepayments and defaults are used establish the starting balance of the next payment. I hope that helps.

Don

P.S. Also, remember that these amortization schedules are from the standpoint of the investor. Even though defaults may be indicated in a given month, the investor is advanced the total P&I from the servicer. From the investor standpoint , defaults can be thought of as just a prepayment, due to the guaranteed P&I. ]]>