Business Template - Mortgage

Document Sample
Business Template - Mortgage Powered By Docstoc
					Mortgage Loan Model
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

Version 1.11

Inputs: Loan Amount Fees added to loan amount Fees charged separately Draw Date Number of Payments Interest Rate per annum Final Loan Balance Sought DayCount

100,000 1,000 1,000 3 Jan 05 120 Pmts 6.00% p.a. 3.00
Actual Days/365.

10.00 years

Outputs: Regular Payment Effective Interest Rate (AER) excl fees APR/AER incl fees (UK Definition) APR (US/Int'l Definition) Final Repayment Date Final Loan Balance Calculated Sensitivity Num Pmts 60 120 180 240 300 Interest Rate 5.00% (1,905) (1,071) (799) (666) (590)

(1,121) 6.17% 6.64% 6.66% 1 Jan 15 0 6.00% (1,952) (1,121) (852) (723) (651)

Act/365 basis Act/365 basis ######## Thursday

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!

Interest Periods (mm.dd) -12.00

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 biannually in arrear on 10thg March and 10th of September.

7.00% (1,999) (1,172) (908) (783) (714)

8.00% (2,047) (1,225) (965) (845) (779)

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.

in arrear, monthly (1 Jan payment of Dec accrual, etc)

Note that the first interest period can be a PARTIAL period, as in the example.

Interest (481) (462) (508) (489) (502) (482) (495) (492) (473) (486) (467) (479) (476) (427) (469) (451) (462) (444) (455) (452) (434) (445) (427) (438) (435) (389) (427) (410) (420) (403) (413) (409) (393) (402) (386) (395) (391) (362) (383) (367) (376) (360) (368) (364) (349) (356) (341) (349) (345) (308) (337) (322) (329) (314) (320) (316) (302) (308) (294) (300) (295) (263) (287) (274) (278) (265) (270) (265)

Principal

Anywhere

3 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep

05 05 05 05 05 05 05 05 05 05 05 05 06 06 06 06 06 06 06 06 06 06 06 06 07 07 07 07 07 07 07 07 07 07 07 07 08 08 08 08 08 08 08 08 08 08 08 08 09 09 09 09 09 09 09 09 09 09 09 09 10 10 10 10 10 10 10 10 10

Illustrative Cashflow SubTotal Loan Fees Payment 101,000 101,000 (2,000) (639) (1,121) (659) (1,121) (613) (1,121) (632) (1,121) (619) (1,121) (638) (1,121) (626) (1,121) (629) (1,121) (648) (1,121) (635) (1,121) (654) (1,121) (642) (1,121) (645) (1,121) (694) (1,121) (652) (1,121) (670) (1,121) (659) (1,121) (677) (1,121) (666) (1,121) (669) (1,121) (687) (1,121) (676) (1,121) (694) (1,121) (683) (1,121) (686) (1,121) (732) (1,121) (694) (1,121) (711) (1,121) (701) (1,121) (718) (1,121) (708) (1,121) (712) (1,121) (728) (1,121) (719) (1,121) (735) (1,121) (726) (1,121) (730) (1,121) (759) (1,121) (738) (1,121) (754) (1,121) (745) (1,121) (761) (1,121) (753) (1,121) (757) (1,121) (772) (1,121) (764) (1,121) (780) (1,121) (772) (1,121) (776) (1,121) (813) (1,121) (784) (1,121) (799) (1,121) (792) (1,121) (807) (1,121) (801) (1,121) (805) (1,121) (819) (1,121) (813) (1,121) (827) (1,121) (821) (1,121) (825) (1,121) (858) (1,121) (834) (1,121) (847) (1,121) (843) (1,121) (856) (1,121) (851) (1,121) (856) (1,121)

Total 99,000 (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121)

Closing Balance 101,000 100,361 99,702 99,089 98,456 97,837 97,199 96,573 95,944 95,296 94,661 94,007 93,365 92,720 92,026 91,374 90,703 90,045 89,368 88,702 88,033 87,346 86,671 85,977 85,294 84,608 83,876 83,183 82,472 81,771 81,054 80,346 79,634 78,906 78,187 77,452 76,726 75,996 75,237 74,499 73,746 73,001 72,240 71,487 70,730 69,958 69,194 68,414 67,642 66,865 66,052 65,268 64,469 63,676 62,869 62,069 61,264 60,445 59,632 58,805 57,984 57,159 56,301 55,467 54,619 53,777 52,921 52,070 51,214

CashFlow For US APR 98,000 (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121)

1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul 1 Aug 1 Sep 1 Oct 1 Nov 1 Dec 1 Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1 Jul

10 10 10 11 11 11 11 11 11 11 11 11 11 11 11 12 12 12 12 12 12 12 12 12 12 12 12 13 13 13 13 13 13 13 13 13 13 13 13 14 14 14 14 14 14 14 14 14 14 14 14 15 15 15 15 15 15 15 15 15 15 15 15 16 16 16 16 16 16 16 16 16 16 16 16 17 17 17 17 17 17 17

(253) (257) (244) (248) (243) (216) (234) (222) (225) (213) (216) (211) (200) (202) (191) (192) (188) (171) (178) (168) (169) (158) (159) (154) (144) (144) (134) (134) (129) (112) (119) (110) (108) (100) (98) (93) (85) (82) (75) (72) (66) (55) (56) (49) (45) (38) (34) (28) (22) (17) (11) (6)

(868) (864) (877) (873) (878) (905) (887) (899) (896) (908) (905) (910) (921) (919) (930) (928) (933) (950) (943) (953) (952) (963) (962) (967) (977) (977) (986) (987) (992) (1,009) (1,002) (1,011) (1,013) (1,021) (1,023) (1,028) (1,036) (1,039) (1,046) (1,049) (1,055) (1,066) (1,065) (1,072) (1,076) (1,083) (1,087) (1,093) (1,099) (1,104) (1,110) (1,115)

(1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121)

(1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121)

50,346 49,481 48,604 47,731 46,853 45,948 45,061 44,163 43,267 42,359 41,454 40,544 39,623 38,704 37,774 36,846 35,913 34,963 34,020 33,067 32,114 31,152 30,190 29,223 28,246 27,269 26,282 25,295 24,303 23,294 22,292 21,281 20,268 19,247 18,224 17,196 16,160 15,122 14,075 13,026 11,971 10,906 9,840 8,768 7,691 6,608 5,521 4,428 3,329 2,225 1,115 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

(1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121) (1,121)


				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:246
posted:2/10/2008
language:English
pages:2