This modelling guide looks at how to model key aspects of a loan – including a choice of debt repayment profiles (level debt service vs level principal).
The accompanying spreadsheet includes a presentation sheet that shows the main features of the loan.
There seems to be an error in the way that the grace period is handled. With a debt payment option of 2 (Fixed payment), when I enter 6 in the dashboard, the first repayment is made on July 31 2015. This means that there are only 5 months of grace (Feb 28, Mar 31, Apr 30, May 31 and Jun 30), not 6. A 6 month grace period means that the first payment is made in the 7th month.
This can also be seen when I enter 1: the first payment is made on Feb 28, which is in fact a 0 month grace period for a loan taken on Jan 31.
Also, when I enter 0 (to see what a loan with no grace period would be) I get a “check” error.
I also get a “check” error with the combination Payment option = 1 and Debt grace period = 1, which should be a valid combination.
Can you fix this error?
Hi, apparently there is an issue with the “financial year ending” calculation. I copied the below from the Excel file (line 94 – 96 from “Time” sheet):
31 janv 15 28 févr 15 31 mars 15 30 avr 15 31 mai 15
1 – – – –
2011 2012 2013 2014 2015
The line items are respectively :
Model period ending
1st model column flag
Financial year ending
This means the month ending 31/1/2015 belongs to financial year 2011 and so on, which I think is an error.
I F11-charted the financial years and see a staircase-like pattern (which is expected) but there are several “off” entries at the beginning. Try and see for yourself to know what I mean.
However I don’t think there is any impact on the actual debt calculation since you did not “annualize” calculations to create an annual model, and the repayment periods are located in the “no-error” zone, i.e. after 30 May 2015. Please check again since you know the model better than me.
I think the error is caused by the fact that the 1st financial year is taken as an input and not derived from the first column of the model. So I could make the 1st column correpond to “1 jan 1999” and the 1st financial year “2015” and no error would be detected.
I do realize that financial year and calendar years are not the same, so implementing a check to see if financial years are “coherent” would not work (company A could end their FY 2013 on 30/6/2013 and company B would end theirs on 30/3/2012). But therefore this leaves the solution to derive the 1st financial year from the 1st column by another calculation block.
Thanks for the great tutorials!
Dear Thang,
Thank you very much for you comment.
Indeed the year typed in InpC!f14 is 2011 and should have been 2015. We solved this by putting the formula =year(f9).
The model is strictly financial and was not made for accountant purposes so no annual summaries were considered.
Kind Regards,
Lior
Hi, thanks for the reply, everything seems to work normally now. I especially like the dashboard and have the following question regarding it:
What is the FAST approach for dashboard (or presentation sheets in general) designing ? Is there a rule of thumb regarding which input to be included on the ‘Inp[]’ sheets and which on the dashboards ?
Also, assuming I would like to move inputs between the dashboard and the Input sheets, how could I do that in an efficient way ? I realize that FAST does not allow for daisy chain links, so replacing the entry in the Input sheet with a link is not a good idea, plus this also breaks a number of other FAST rules.
Thang sent us the same question on a different forum but I am sharing Mala Khetarpal’s response here for everyone’s benefit. Mala says:
Dashboard/presentation sheets are described as ‘user’ sheets, and, therefore, we do not sternly follow FAST guidelines while constructing these sheets. These sheets are designed in a manner which makes it easy for the users to operate/use them. Usually a few key inputs and key outputs (as determined by the user) are placed in Dashboard sheets, with Input sheets containing all the other inputs.
As for moving inputs between Dashboard and Input sheets, the approach we follow in our models is to channel all the inputs to the calculation sheets via Input sheet. Therefore, the inputs lying in Dashboard sheet are placed as links in the Input sheet, from where they are further used in the calculation sheets. No inputs are thus linked directly from the Dashboard sheet. This approach does create daisy chain links; however, an exception to the rule here is necessary to ensure that all model inputs lie in dedicated input sheet/s. Otherwise, the model would contain inputs in two sheets (Input and Dashboard), and one would need to spend time to locate the inputs.
Following the approach suggested above, if you wish to move some inputs to Dashboard sheet, you just need to copy the input lines and paste them in Dashboard sheet. You can then create a link to them from Dashboard sheet to Input sheet.
Does this answer your question?
Kind regards,
Mala Khetarpal