2014 Round 1: Precise Debt Modeling

Description
This workbook is taken from the 1st round of the 2014 Modeloff financial modeling world chamionships.

Follow the instructions in order to complete the model:

INTRODUCTION
You work at a bank and have been asked to prepare a spreadsheet that can generate loan schedules for personal loans taken out by the bank’s customers. The loan details are as follows:

• The loan duration can be for any number of months up to 72 months.
• Drawdowns occur as a single drawdown for the full balance at the beginning of the loan.
• The drawdown can occur on any Business Day. A Business Day is defined as a weekday (Monday to Friday) that is not a Holiday. The list of Holidays has been provided to you.
• The Actual Payment Date shall be based on the Regular Payment Date, but adjusted to be a Business Day as described below.
• Loan payments are made monthly in arrears, on the Actual Payment Date each month, with the first payment one month after drawdown.
• The Regular Payment Date each month will be the same DAY of the month as the loan drawdown (for example, if the drawdown was the 8th of October, then the Regular Payment Date will be the 8th of each month).
• The Actual Payment Date will equal the Regular Payment Date, subject to the following three conditions:

Condition 1: If the Regular Payment Date is not a Business Day, then the Actual Payment Date will be made on the first Business Day after the regular payment date, except where that Business Day would be in a new calendar month.

Condition 2: If Condition 1 results in a new calendar month, the Actual Payment Date shall be the last Business Day of the calendar month (which, by definition, will be before the Regular Payment Date)

Condition 3: If the Regular Payment Date is the 29th, 30th or 31st of the month then, where a month has fewer days (e.g. February), the Actual Payment Date will be the last Business Day of the calendar month.

• The Monthly Payment Amount shall be the same for each period, and include both an interest portion and a principal portion. Therefore, the split between interest and principal will change from period to period.
• Interest due shall be calculated on an Actual / 365 basis based on the number of days between successive Actual Payment Dates.
• Do not round any of your calculations.

Your manager has also given you the following advice for preparing your model:
• It is expected you will need to use Excel’s Goal Seek tool or equivalent functionality in order to find the Monthly Payment Amount. Due to the interest periods having a different number of days from month to month, you will not be able to solve this problem with the PMT, PPMT or IPMT functions.

This package also includes a worked solution from MODEL CITIZN

Allotted time: 40 minutes

Once finished, feel free to upload your model to your own dedicated author channel!