Business Template - Mortgage

Mortgage Loan Model Version 1.11 For loan mortgages You can insert extra rows , so long as you copy the formula down ACROSS the region where you inserted the rows. Features: Partial first period capability, Business Day Capability, any form of interest daycount, sensitivity matrix Inputs: Outputs: Loan Amount 100,000 Regular Payment (1,121) Fees added to loan amount 1,000 Effective Interest Rate (AER) excl fees 6.17% Act/365 basis Fees charged separately 1,000 APR/AER incl fees (UK Definition) 6.64% Act/365 basis Draw Date 3 Jan 05 APR (US/Int'l Definition) 6.66% ######## Number of Payments 120 Pmts 10.00 years Final Repayment Date 1 Jan 15 Thursday Interest Rate per annum 6.00% p.a. Final Loan Balance Calculated 0 Final Loan Balance Sought Sensitivity Interest Rate DayCount 3.00 Num Pmts 5.00% 6.00% 7.00% 8.00% Actual Days/365. 60 (1,905) (1,952) (1,999) (2,047) 120 (1,071) (1,121) (1,172) (1,225) Interest 180 (799) (852) (908) (965) Periods (mm.dd) 240 (666) (723) (783) (845) -12.00 300 (590) (651) (714) (779) in arrear, monthly (1 Jan payment of Dec accrual, etc) Illustrative Cashflow Note that the first interest period SubTotal Closing CashFlow can be a PARTIAL period, as in the Interest Principal Loan Fees Total Balance For US APR example. Payment 3 Jan 05 101,000 101,000 (2,000) 99,000 101,000 98,000 1 Feb 05 (481) (639) (1,121) (1,121) 100,361 (1,121) 1 Mar 05 (462) (659) (1,121) (1,121) 99,702 (1,121) 1 Apr 05 (508) (613) (1,121) (1,121) 99,089 (1,121) 1 May 05 (489) (632) (1,121) (1,121) 98,456 (1,121) 1 Jun 05 (502) (619) (1,121) (1,121) 97,837 (1,121) 1 Jul 05 (482) (638) (1,121) (1,121) 97,199 (1,121) 1 Aug 05 (495) (626) (1,121) (1,121) 96,573 (1,121) 1 Sep 05 (492) (629) (1,121) (1,121) 95,944 (1,121) 1 Oct 05 (473) (648) (1,121) (1,121) 95,296 (1,121) 1 Nov 05 (486) (635) (1,121) (1,121) 94,661 (1,121) 1 Dec 05 (467) (654) (1,121) (1,121) 94,007 (1,121) 1 Jan 06 (479) (642) (1,121) (1,121) 93,365 (1,121) 1 Feb 06 (476) (645) (1,121) (1,121) 92,720 (1,121) 1 Mar 06 (427) (694) (1,121) (1,121) 92,026 (1,121) 1 Apr 06 (469) (652) (1,121) (1,121) 91,374 (1,121) 1 May 06 (451) (670) (1,121) (1,121) 90,703 (1,121) 1 Jun 06 (462) (659) (1,121) (1,121) 90,045 (1,121) 1 Jul 06 (444) (677) (1,121) (1,121) 89,368 (1,121) 1 Aug 06 (455) (666) (1,121) (1,121) 88,702 (1,121) 1 Sep 06 (452) (669) (1,121) (1,121) 88,033 (1,121) 1 Oct 06 (434) (687) (1,121) (1,121) 87,346 (1,121) 1 Nov 06 (445) (676) (1,121) (1,121) 86,671 (1,121) 1 Dec 06 (427) (694) (1,121) (1,121) 85,977 (1,121) 1 Jan 07 (438) (683) (1,121) (1,121) 85,294 (1,121) 1 Feb 07 (435) (686) (1,121) (1,121) 84,608 (1,121) 1 Mar 07 (389) (732) (1,121) (1,121) 83,876 (1,121) 1 Apr 07 (427) (694) (1,121) (1,121) 83,183 (1,121) 1 May 07 (410) (711) (1,121) (1,121) 82,472 (1,121) 1 Jun 07 (420) (701) (1,121) (1,121) 81,771 (1,121) 1 Jul 07 (403) (718) (1,121) (1,121) 81,054 (1,121) 1 Aug 07 (413) (708) (1,121) (1,121) 80,346 (1,121) 1 Sep 07 (409) (712) (1,121) (1,121) 79,634 (1,121) 1 Oct 07 (393) (728) (1,121) (1,121) 78,906 (1,121) 1 Nov 07 (402) (719) (1,121) (1,121) 78,187 (1,121) 1 Dec 07 (386) (735) (1,121) (1,121) 77,452 (1,121) 1 Jan 08 (395) (726) (1,121) (1,121) 76,726 (1,121) 1 Feb 08 (391) (730) (1,121) (1,121) 75,996 (1,121) 1 Mar 08 (362) (759) (1,121) (1,121) 75,237 (1,121) 1 Apr 08 (383) (738) (1,121) (1,121) 74,499 (1,121) 1 May 08 (367) (754) (1,121) (1,121) 73,746 (1,121) 1 Jun 08 (376) (745) (1,121) (1,121) 73,001 (1,121) 1 Jul 08 (360) (761) (1,121) (1,121) 72,240 (1,121) 1 Aug 08 (368) (753) (1,121) (1,121) 71,487 (1,121) 1 Sep 08 (364) (757) (1,121) (1,121) 70,730 (1,121) 1 Oct 08 (349) (772) (1,121) (1,121) 69,958 (1,121) 1 Nov 08 (356) (764) (1,121) (1,121) 69,194 (1,121) 1 Dec 08 (341) (780) (1,121) (1,121) 68,414 (1,121) 1 Jan 09 (349) (772) (1,121) (1,121) 67,642 (1,121) 1 Feb 09 (345) (776) (1,121) (1,121) 66,865 (1,121) 1 Mar 09 (308) (813) (1,121) (1,121) 66,052 (1,121) 1 Apr 09 (337) (784) (1,121) (1,121) 65,268 (1,121) 1 May 09 (322) (799) (1,121) (1,121) 64,469 (1,121) 1 Jun 09 (329) (792) (1,121) (1,121) 63,676 (1,121) 1 Jul 09 (314) (807) (1,121) (1,121) 62,869 (1,121) 1 Aug 09 (320) (801) (1,121) (1,121) 62,069 (1,121) 1 Sep 09 (316) (805) (1,121) (1,121) 61,264 (1,121) 1 Oct 09 (302) (819) (1,121) (1,121) 60,445 (1,121) 1 Nov 09 (308) (813) (1,121) (1,121) 59,632 (1,121) 1 Dec 09 (294) (827) (1,121) (1,121) 58,805 (1,121) 1 Jan 10 (300) (821) (1,121) (1,121) 57,984 (1,121) 1 Feb 10 (295) (825) (1,121) (1,121) 57,159 (1,121) 1 Mar 10 (263) (858) (1,121) (1,121) 56,301 (1,121) 1 Apr 10 (287) (834) (1,121) (1,121) 55,467 (1,121) 1 May 10 (274) (847) (1,121) (1,121) 54,619 (1,121) 1 Jun 10 (278) (843) (1,121) (1,121) 53,777 (1,121) 1 Jul 10 (265) (856) (1,121) (1,121) 52,921 (1,121) 1 Aug 10 (270) (851) (1,121) (1,121) 52,070 (1,121) 1 Sep 10 (265) (856) (1,121) (1,121) 51,214 (1,121) Interest Periods: Describes when interest is calculated (accrues). You may need to consult the Business Functions Help File for more information. Basically, -12 means monthly in arrear, -4 means quarterly in arrear, etc. You can also specify a series of values in mm.dd format, for example -3.10, -9.10 means biannuuall in arrear on 10thg March and 10th of September. DayCount: Describes how the bank calculates its interest. You may need to consult the Business Functions Help File for more information. The most common options are 3 (Actual/365), 0 (30/360) and 6 (ACT/ACT in period). If you want to adjust for Business Days: Try the following. Insert '20' into the first spare field in the interest periods and make sure DrawDate (cell E12) is a Business Day. Watch the spreadsheet adjust your payment days for weekends! Note: The cashflow below is actually just a check to demonstrate the solution. All the work is actually done in a single function call, in cell L10.1 Oct 10 (253) (868) (1,121) (1,121) 50,346 (1,121) 1 Nov 10 (257) (864) (1,121) (1,121) 49,481 (1,121) 1 Dec 10 (244) (877) (1,121) (1,121) 48,604 (1,121) 1 Jan 11 (248) (873) (1,121) (1,121) 47,731 (1,121) 1 Feb 11 (243) (878) (1,121) (1,121) 46,853 (1,121) 1 Mar 11 (216) (905) (1,121) (1,121) 45,948 (1,121) 1 Apr 11 (234) (887) (1,121) (1,121) 45,061 (1,121) 1 May 11 (222) (899) (1,121) (1,121) 44,163 (1,121) 1 Jun 11 (225) (896) (1,121) (1,121) 43,267 (1,121) 1 Jul 11 (213) (908) (1,121) (1,121) 42,359 (1,121) 1 Aug 11 (216) (905) (1,121) (1,121) 41,454 (1,121) 1 Sep 11 (211) (910) (1,121) (1,121) 40,544 (1,121) 1 Oct 11 (200) (921) (1,121) (1,121) 39,623 (1,121) 1 Nov 11 (202) (919) (1,121) (1,121) 38,704 (1,121) 1 Dec 11 (191) (930) (1,121) (1,121) 37,774 (1,121) 1 Jan 12 (192) (928) (1,121) (1,121) 36,846 (1,121) 1 Feb 12 (188) (933) (1,121) (1,121) 35,913 (1,121) 1 Mar 12 (171) (950) (1,121) (1,121) 34,963 (1,121) 1 Apr 12 (178) (943) (1,121) (1,121) 34,020 (1,121) 1 May 12 (168) (953) (1,121) (1,121) 33,067 (1,121) 1 Jun 12 (169) (952) (1,121) (1,121) 32,114 (1,121) 1 Jul 12 (158) (963) (1,121) (1,121) 31,152 (1,121) 1 Aug 12 (159) (962) (1,121) (1,121) 30,190 (1,121) 1 Sep 12 (154) (967) (1,121) (1,121) 29,223 (1,121) 1 Oct 12 (144) (977) (1,121) (1,121) 28,246 (1,121) 1 Nov 12 (144) (977) (1,121) (1,121) 27,269 (1,121) 1 Dec 12 (134) (986) (1,121) (1,121) 26,282 (1,121) 1 Jan 13 (134) (987) (1,121) (1,121) 25,295 (1,121) 1 Feb 13 (129) (992) (1,121) (1,121) 24,303 (1,121) 1 Mar 13 (112) (1,009) (1,121) (1,121) 23,294 (1,121) 1 Apr 13 (119) (1,002) (1,121) (1,121) 22,292 (1,121) 1 May 13 (110) (1,011) (1,121) (1,121) 21,281 (1,121) 1 Jun 13 (108) (1,013) (1,121) (1,121) 20,268 (1,121) 1 Jul 13 (100) (1,021) (1,121) (1,121) 19,247 (1,121) 1 Aug 13 (98) (1,023) (1,121) (1,121) 18,224 (1,121) 1 Sep 13 (93) (1,028) (1,121) (1,121) 17,196 (1,121) 1 Oct 13 (85) (1,036) (1,121) (1,121) 16,160 (1,121) 1 Nov 13 (82) (1,039) (1,121) (1,121) 15,122 (1,121) 1 Dec 13 (75) (1,046) (1,121) (1,121) 14,075 (1,121) 1 Jan 14 (72) (1,049) (1,121) (1,121) 13,026 (1,121) 1 Feb 14 (66) (1,055) (1,121) (1,121) 11,971 (1,121) 1 Mar 14 (55) (1,066) (1,121) (1,121) 10,906 (1,121) 1 Apr 14 (56) (1,065) (1,121) (1,121) 9,840 (1,121) 1 May 14 (49) (1,072) (1,121) (1,121) 8,768 (1,121) 1 Jun 14 (45) (1,076) (1,121) (1,121) 7,691 (1,121) 1 Jul 14 (38) (1,083) (1,121) (1,121) 6,608 (1,121) 1 Aug 14 (34) (1,087) (1,121) (1,121) 5,521 (1,121) 1 Sep 14 (28) (1,093) (1,121) (1,121) 4,428 (1,121) 1 Oct 14 (22) (1,099) (1,121) (1,121) 3,329 (1,121) 1 Nov 14 (17) (1,104) (1,121) (1,121) 2,225 (1,121) 1 Dec 14 (11) (1,110) (1,121) (1,121) 1,115 (1,121) 1 Jan 15 (6) (1,115) (1,121) (1,121) 0 (1,121) 1 Feb 15 0 1 Mar 15 0 1 Apr 15 0 1 May 15 0 1 Jun 15 0 1 Jul 15 0 1 Aug 15 0 1 Sep 15 0 1 Oct 15 0 1 Nov 15 0 1 Dec 15 0 1 Jan 16 0 1 Feb 16 0 1 Mar 16 0 1 Apr 16 0 1 May 16 0 1 Jun 16 0 1 Jul 16 0 1 Aug 16 0 1 Sep 16 0 1 Oct 16 0 1 Nov 16 0 1 Dec 16 0 1 Jan 17 0 1 Feb 17 0 1 Mar 17 0 1 Apr 17 0 1 May 17 0 1 Jun 17 0 1 Jul 17