Bond, Loan or Note Make Whole Calculator

All Industries, Asset Management, Banking, Financial Model, Financial Services, General Excel Financial Models, Loans & Lending Businesses, Microfinance, Peer to Peer Lending (P2P), Personal Finance, Stock Market

Accounting, Amortization, Bad Debt, Bonds, Budget, Budgeting, Business Valuation, Calculator, Debt Amortization, Debt Schedule, Debt Security, Discount Rate, Financial Analysis, Financial Modeling, Forecasting, Interest Accrual, NPV (Net Present Value), Pricing, Scenario Analysis, Valuation

General considerations for the model:
• All blue elements are inputs you need to adjust according to your assessment.
• Blue elements with a yellow fill identify critical drivers for the model.
• Cash Flow sensitivity analysis feeds from all your inputs.

What is the model for?

The model is designed to evaluate a make-whole calculator for a loan or note, such as bonds or structured notes. It is intended to be straightforward, so you can make an informed decision in a more comprehensive way, along with a manual calculation so you can assess and validate your assumptions.

General concept of the model:

The Make-Whole concept is described as the exercise of deriving the redemption value of an investment, typically in form of debt. The most common way of doing it is by calculating the present value of the remaining cash flows using a discount rate composed of a United States Treasury equivalent rate plus a spread.

The template is divided into three analysis sections, the input, the cash flow valuation, and the output. At the same time, there’s a fourth section named the calculation sanity check to validate that your model is running correctly.

What does each section do?

Along the sections, you will find all the required elements to make your assessment as described:
In the input section, you will have the general elements of the instrument. The model is designed to be used regardless of if you are valuating a note, loan, or bond. Here you will be able to layout your assumptions and set up your sensitivities.

The Cash Flow section is designed to give you visibility on each of the remaining coupons. The model has up to 100 future coupons with the frequency fully adjustable on the input section. Here you will have the cash flows for all the running scenarios.

Output section, here, you will see the summary of your base scenario plus your two sensitivities. All of the key elements for decision-making are shown on the output, such as general elements of the note, Make Whole spread, the yield, and the price to pay.

Lastly, the fourth and final section is a small sanity check for the model to balance out. This is done by replicating both methods for calculating the make whole manually and by using the excel preset function price.

For any inquiries on the model or specific modifications and advisory, please feel free to reach out to the support team or use the Contact Author button.

Happy modeling!

You must log in to submit a review.