Business Template - Flexible Mortgage Loan with Stepped Payments

Mortgage where Available Debt Service Matches Interest and Principle This model shows a mortgage loan where you decide how much is paid towards combined interest and principal, and the model works out the individual periods interest and principal, until the loan is paid off. Whilst this can be used for a simple case of a single loan advance, a single interest rate, a single PMT (debt service) figure, the use of the Mortgage() function enables you to go much further. These are the sorts of things you can do: Several drawdowns at different times -as many as you like. Interest rate that varies over time, quarter by quarter if you need to, or even part-way through a quarter. Debt Service input on a stepping profile. The dates for loan need not match those of the debt service -what will happen is that the debt service will just accumulate until its used at the next The function gives the same answer regardless of the timebase you use, on the left hand side in this model. So you can model a quarterly loan on To see what the model (and the function) can do, we suggest the following as exercises: Change the base to 'As Loan Dates' and then to 12 (months). Let the interest rate step. Scheduled Loan Advances Interest Rates Advance Net FromDates Interest Dates Advances Interest Simple 1 Feb 01 500 1 Feb 01 10.00% 1 Feb 02 500 1 May 01 10.00% Longstop Repayment Date 1 Jan 30 DayCount 3 Actual Days/365. Periods -1.01 -7.01 Period Principal Interest Total Balance End Comm. Period 1 5 1 Feb 01 476 476 476 1 May 01 (55) (20) (75) 420 1 Aug 01 420 1 Nov 01 (54) (21) (75) 366 1 Feb 02 500 500 866 1 May 02 (36) (39) (75) 830 1 Aug 02 830 1 Nov 02 (33) (42) (75) 797 1 Feb 03 797 1 May 03 (35) (40) (75) 762 1 Aug 03 762 1 Nov 03 (37) (38) (75) 725 1 Feb 04 725 1 May 04 (39) (36) (75) 686 1 Aug 04 686 1 Nov 04 (40) (35) (75) 646 1 Feb 05 646 1 May 05 (43) (32) (75) 603 1 Aug 05 603 1 Nov 05 (48) (30) (78) 555 1 Feb 06 555 1 May 06 (57) (28) (85) 497 1 Aug 06 497 1 Nov 06 (60) (25) (85) 437 1 Feb 07 437 1 May 07 (63) (22) (85) 374 1 Aug 07 374 1 Nov 07 (66) (19) (85) 308 1 Feb 08 308 1 May 08 (70) (15) (85) 238 1 Aug 08 238 1 Nov 08 (73) (12) (85) 165 1 Feb 09 165 1 May 09 (77) (8) (85) 891 Aug 09 89 1 Nov 09 (81) (4) (85) 8 1 Feb 10 8 1 May 10 (8) (0) (8) 1 Aug 10 1 Nov 10 1 Feb 11 1 May 11 1 Aug 11 1 Nov 11 1 Feb 12 1 May 12 1 Aug 12 (466) (466)Interest and Principle rate, a single PMT (debt service) figure, that the debt service will just accumulate until its used at the next loan date. left hand side in this model. So you can model a quarterly loan on an annual timebase. Start of Projection 1 Feb 01 Base of the cashflow 3 months Available Cash Flow for Debt Service From Annual Debt Dates Service Rates 1 Feb 01 150 1 Feb 06 170 1 Feb 11 200 1 Feb 16 230 Finish 1 Jan 25 Main Period:Actual/Actual (Within Period),Stub Periods:Actual Days/365. DayCount 6.03 Periods 4