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: