Troubled Debt Restructuring (TDR) – Present Value

A troubled debt restructuring (TDR) is defined as a debt restructuring in which a creditor, for economic or legal reasons related to a debtor’s financial difficulties, grants a concession to the debtor that it would not otherwise consider. —Wikipedia

I have not used the linked spreadsheet in any real-world applications, so there may be errors I am not aware of.

For a mortgage or commercial loan, this would normally mean modifying the original loan. Amoung other things, the modifications could be:

Skip one or more payments

Change the interest rate for one or more payments

Pay interest only for one or more payments

Increase the number of payments

Require a forbearance amount be paid


TRD modifications can produce a loss of income for the lender. One way to recognize the potential losses (impairment), is to calculate the present value of the differences in cash flows between the modified loan, and the original loan. The discount rate used could be the rate on the original loan, or the prevailing market rate.

First an amortization schedule of the original loan is produced using the inputs in the yellow cells:

Next, the inputs in yellow are used to create another amortization schedule along with the modifications. Again, only the yellow cells are inputs.┬áThe modified loan can use any number of months to calculate the payments on the modified loan, but defaults to the months remaining in the original loan. To use a different number of months for this calculation, enter the months in the yellow cell to the right. Next, if the lender agrees to allow interest only payments to the first few months, enter the number of months in the “Interest Only Months” yellow cell. The “New Term Months” are not an input, but is the sum of the terms in the “Modified Rates” table. Hopefully, the rest of the inputs are self-explanatory:

P.S. The light colored letters next to some cells are the named values used in the calculations.

The two amortizations and the differences in cash flows, are represented on the “Present Value” sheet:

Download: TDR

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

Leave a Reply

Your email address will not be published. Required fields are marked *