Debt Example 1 Question
You want to create a flexible debt schedule.
- In B8 calculate the repayment amount
- In F13 row link the opening balance to the prior periods closing balance.
- In F15 row calculate the interest charge for the period
- In F17 row include the suggested repayment amount
- Check in AC19 that your loan comes to zero.
Debt Example 2 Question
You now want to separate the interest from the capital repayment
- In F16 row, show the portion of interest that is repaid (generally just the opposite sign of the interest charged)
- In F17 row, calculate the capital portion of the repayment. You can do it directly or use the helper rows in 23 and 24
Debt Example 3 Question
You now want to allow for ad hoc prepayments
- In F9 row, include a counter that goes backwards from 24 i.e. 24, 23, 22, …
- In F24 row, calculate the capital portion repaid based on the remaining life of the loan shown in row 9
- In row 17 link up the capital portion
- In F18 row add some prepayments in and check that no matter what you add in, at the end of the loan period the balance is zero
Debt Example 4 Question
You now want to allow for interest and capital holidays
- In F16 row, include a check that only repays interest after the number of months shown in B29 (interest repayment holiday)
- In F17 row, include a check that only repays capital after the the number of months shown in B30 (capital repayment holiday)
- Play with the inputs to see how the model will allow for detailed debt calculations
Debt Example 5 Question
This is the same as example 4 except the financier wants all the capitalised interest to be paid in the month that we start repaying interest. So for example if we have an interest holiday of 3 months, then in month 4 we need to repay all the interest from month 1 to 4 and then from then on just repay that month’s interest.
Debt Example 6 Question
We have built an annual model and need to determine the interest/ capital split on repayments of a monthly loan. In rows 7 to 13 we have shown the wrong way to do it (use the opening balance and assume interest for the full year on that and the difference is then the capital)
In row 20 to 22 use the correct method.
When done, note that the actual repayment is the same but the split (and therefore tax and certain ratios) will be different.
Debt Example 7 Question
We have determined that prices are set to DECREASE in the next 12 months by an effective 6%. We need to calculate what the monthly decrease will be to result in an effective decrease of 6% for the year.
To see the full course contents click here.