Docstoc

Quarterly Mortgage Payment Formula

Document Sample
Quarterly Mortgage Payment Formula Powered By Docstoc
					                  Mortgage Loan Model, with ad-hoc extra Repayments                                                                                                                                                            Version 1.1
                                                                                                                                                                                                                         Requires BF 1.25+
                  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, multiple ad hoc repaymnets



                  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.73%              Act/365 basis
                  Draw Date                                      3 Jan 05                                                                       APR (US/Int'l Definition)                                    6.80%               #########
                  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
                                                                                     DayCount: Describes how the bank calculates its interest. You
                                                                                                                                                              Extra Capital Repayments:                                            Change In
                                                                                     may need to consult the Business Functions Help File for more
                  DayCount                                          3.00             information. The most common options are 3 (Actual/365), 0                   Date       Payment                        Num Prds               Reg. Pmt
                                                             Actual Days/365.        (30/360) and 6 (ACT/ACT in period).                                        1 Apr 05     (10,000)                        117.00                   113
                                                                                                                                                                1 Apr 07     (10,000)                          93                     135
                                                                  Interest
                                                             Periods (mm.dd)
           If you want to adjust for Business Days: Try            -12.00            Interest Periods: Describes when interest is calculated (accrues).
           the following. Insert '20' into the first spare                           You may need to consult the Business Functions Help File for
           field in the interest periods and make sure                               more information. Basically, -12 means monthly in arrear, -4
           DrawDate (cell E12) is a Business Day.                                    means quarterly in arrear, etc. You can also specify a series of
           Watch the spreadsheet adjust your payment                                 values in mm.dd format, for example -3.10, -9.10 means bi-
           days for weekends!                                                        annually in arrear on 10th March and 10th of September.



                                                              in arrear, monthly (1 Jan payment of Dec accrual, etc)
                                                                                                                                                               Note: The cashflow below is actually just a check to demonstrate the
                                                                                                                                                               solution. All the work is actually done in a single function calls, in
                                                                                                                                                               cells L10 and M18:M29.
                                                                                         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)            (10,613)       (11,121)                                        (11,121)                            89,089                                       (11,121)
                         1 May    05                               (439)              (568)         (1,008)                                         (1,008)                            88,520                                        (1,008)
                         1 Jun    05                               (451)              (557)         (1,008)                                         (1,008)                            87,963                                        (1,008)
                          1 Jul   05                               (434)              (574)         (1,008)                                         (1,008)                            87,389                                        (1,008)
                         1 Aug    05                               (445)              (562)         (1,008)                                         (1,008)                            86,827                                        (1,008)
                         1 Sep    05                               (442)              (565)         (1,008)                                         (1,008)                            86,262                                        (1,008)
                          1 Oct   05                               (425)              (582)         (1,008)                                         (1,008)                            85,679                                        (1,008)
                         1 Nov    05                               (437)              (571)         (1,008)                                         (1,008)                            85,108                                        (1,008)
                         1 Dec    05                               (420)              (588)         (1,008)                                         (1,008)                            84,520                                        (1,008)
                          1 Jan   06                               (431)              (577)         (1,008)                                         (1,008)                            83,943                                        (1,008)
                         1 Feb    06                               (428)              (580)         (1,008)                                         (1,008)                            83,363                                        (1,008)
                         1 Mar    06                               (384)              (624)         (1,008)                                         (1,008)                            82,738                                        (1,008)
                         1 Apr    06                               (422)              (586)         (1,008)                                         (1,008)                            82,152                                        (1,008)
                         1 May    06                               (405)              (603)         (1,008)                                         (1,008)                            81,550                                        (1,008)
Anywhere                 1 Jun    06                               (416)              (592)         (1,008)                                         (1,008)                            80,957                                        (1,008)
                          1 Jul   06                               (399)              (609)         (1,008)                                         (1,008)                            80,349                                        (1,008)
                         1 Aug    06                               (409)              (598)         (1,008)                                         (1,008)                            79,750                                        (1,008)
                         1 Sep    06                               (406)              (601)         (1,008)                                         (1,008)                            79,149                                        (1,008)
                          1 Oct   06                               (390)              (618)         (1,008)                                         (1,008)                            78,531                                        (1,008)
                         1 Nov    06                               (400)              (608)         (1,008)                                         (1,008)                            77,924                                        (1,008)
                         1 Dec    06                               (384)              (624)         (1,008)                                         (1,008)                            77,300                                        (1,008)
                          1 Jan   07                               (394)              (614)         (1,008)                                         (1,008)                            76,686                                        (1,008)
                         1 Feb    07                               (391)              (617)         (1,008)                                         (1,008)                            76,069                                        (1,008)
                         1 Mar    07                               (350)              (658)         (1,008)                                         (1,008)                            75,412                                        (1,008)
                         1 Apr    07                               (384)            (10,624)       (11,008)                                        (11,008)                            64,788                                       (11,008)
                         1 May    07                               (320)              (554)          (873)                                           (873)                             64,234                                         (873)
                         1 Jun    07                               (327)              (546)          (873)                                           (873)                             63,689                                         (873)
                          1 Jul   07                               (314)              (559)          (873)                                           (873)                             63,130                                         (873)
                         1 Aug    07                               (322)              (551)          (873)                                           (873)                             62,578                                         (873)
                         1 Sep    07                               (319)              (554)          (873)                                           (873)                             62,024                                         (873)
                          1 Oct   07                               (306)              (567)          (873)                                           (873)                             61,457                                         (873)
                         1 Nov    07                               (313)              (560)          (873)                                           (873)                             60,897                                         (873)
                         1 Dec    07                               (300)              (573)          (873)                                           (873)                             60,324                                         (873)
                          1 Jan   08                               (307)              (566)          (873)                                           (873)                             59,759                                         (873)
                         1 Feb    08                               (305)              (569)          (873)                                           (873)                             59,190                                         (873)
                         1 Mar    08                               (282)              (591)          (873)                                           (873)                             58,599                                         (873)
                         1 Apr    08                               (299)              (574)          (873)                                           (873)                             58,025                                         (873)
                         1 May    08                               (286)              (587)          (873)                                           (873)                             57,438                                         (873)
                         1 Jun    08                               (293)              (580)          (873)                                           (873)                             56,858                                         (873)
                          1 Jul   08                               (280)              (593)          (873)                                           (873)                             56,265                                         (873)
                         1 Aug    08                               (287)              (586)          (873)                                           (873)                             55,679                                         (873)
                         1 Sep    08                               (284)              (589)          (873)                                           (873)                             55,089                                         (873)
                          1 Oct   08                               (272)              (601)          (873)                                           (873)                             54,488                                         (873)
                         1 Nov    08                               (278)              (595)          (873)                                           (873)                             53,892                                         (873)
                         1 Dec    08                               (266)              (607)          (873)                                           (873)                             53,285                                         (873)
                          1 Jan   09                               (272)              (602)          (873)                                           (873)                             52,684                                         (873)
                         1 Feb    09                               (268)              (605)          (873)                                           (873)                             52,079                                         (873)
                         1 Mar    09                               (240)              (633)          (873)                                           (873)                             51,446                                         (873)
                         1 Apr    09                               (262)              (611)          (873)                                           (873)                             50,835                                         (873)
                         1 May    09                               (251)              (622)          (873)                                           (873)                             50,212                                         (873)
                         1 Jun    09                               (256)              (617)          (873)                                           (873)                             49,595                                         (873)
                          1 Jul   09                               (245)              (628)          (873)                                           (873)                             48,967                                         (873)
                         1 Aug    09                               (250)              (624)          (873)                                           (873)                             48,343                                         (873)
                         1 Sep    09                               (246)              (627)          (873)                                           (873)                             47,716                                         (873)
                          1 Oct   09                               (235)              (638)          (873)                                           (873)                             47,079                                         (873)
                         1 Nov    09                               (240)              (633)          (873)                                           (873)                             46,445                                         (873)
                         1 Dec    09                               (229)              (644)          (873)                                           (873)                             45,801                                         (873)
                          1 Jan   10                               (233)              (640)          (873)                                           (873)                             45,162                                         (873)
                         1 Feb    10                               (230)              (643)          (873)                                           (873)                             44,519                                         (873)
                         1 Mar    10                               (205)              (668)          (873)                                           (873)                             43,851                                         (873)
                         1 Apr    10                               (223)              (650)          (873)                                           (873)                             43,201                                         (873)
                         1 May    10                               (213)              (660)          (873)                                           (873)                             42,541                                         (873)
                         1 Jun    10                               (217)              (656)          (873)                                           (873)                             41,885                                         (873)
                          1 Jul   10                               (207)              (667)          (873)                                           (873)                             41,218                                         (873)
                         1 Aug    10                               (210)              (663)          (873)                                           (873)                             40,555                                         (873)
                         1 Sep    10                               (207)              (666)          (873)                                           (873)                             39,889                                         (873)
                          1 Oct   10                               (197)              (676)          (873)                                           (873)                             39,212                                         (873)
                         1 Nov    10                               (200)              (673)          (873)                                           (873)                             38,539                                         (873)
                         1 Dec    10                               (190)              (683)          (873)                                           (873)                             37,856                                         (873)
                          1 Jan   11                               (193)              (680)          (873)                                           (873)                             37,176                                         (873)
1 Feb    11   (189)   (684)   (873)   (873)   36,492   (873)
1 Mar    11   (168)   (705)   (873)   (873)   35,787   (873)
1 Apr    11   (182)   (691)   (873)   (873)   35,097   (873)
1 May    11   (173)   (700)   (873)   (873)   34,397   (873)
1 Jun    11   (175)   (698)   (873)   (873)   33,699   (873)
 1 Jul   11   (166)   (707)   (873)   (873)   32,992   (873)
1 Aug    11   (168)   (705)   (873)   (873)   32,287   (873)
1 Sep    11   (165)   (709)   (873)   (873)   31,578   (873)
 1 Oct   11   (156)   (717)   (873)   (873)   30,861   (873)
1 Nov    11   (157)   (716)   (873)   (873)   30,145   (873)
1 Dec    11   (149)   (724)   (873)   (873)   29,421   (873)
 1 Jan   12   (150)   (723)   (873)   (873)   28,698   (873)
1 Feb    12   (146)   (727)   (873)   (873)   27,971   (873)
1 Mar    12   (133)   (740)   (873)   (873)   27,231   (873)
1 Apr    12   (139)   (734)   (873)   (873)   26,497   (873)
1 May    12   (131)   (742)   (873)   (873)   25,755   (873)
1 Jun    12   (131)   (742)   (873)   (873)   25,013   (873)
 1 Jul   12   (123)   (750)   (873)   (873)   24,263   (873)
1 Aug    12   (124)   (749)   (873)   (873)   23,514   (873)
1 Sep    12   (120)   (753)   (873)   (873)   22,760   (873)
 1 Oct   12   (112)   (761)   (873)   (873)   22,000   (873)
1 Nov    12   (112)   (761)   (873)   (873)   21,239   (873)
1 Dec    12   (105)   (768)   (873)   (873)   20,470   (873)
 1 Jan   13   (104)   (769)   (873)   (873)   19,701   (873)
1 Feb    13   (100)   (773)   (873)   (873)   18,929   (873)
1 Mar    13    (87)   (786)   (873)   (873)   18,143   (873)
1 Apr    13    (92)   (781)   (873)   (873)   17,362   (873)
1 May    13    (86)   (787)   (873)   (873)   16,575   (873)
1 Jun    13    (84)   (789)   (873)   (873)   15,786   (873)
 1 Jul   13    (78)   (795)   (873)   (873)   14,991   (873)
1 Aug    13    (76)   (797)   (873)   (873)   14,194   (873)
1 Sep    13    (72)   (801)   (873)   (873)   13,394   (873)
 1 Oct   13    (66)   (807)   (873)   (873)   12,587   (873)
1 Nov    13    (64)   (809)   (873)   (873)   11,778   (873)
1 Dec    13    (58)   (815)   (873)   (873)   10,963   (873)
 1 Jan   14    (56)   (817)   (873)   (873)   10,145   (873)
1 Feb    14    (52)   (821)   (873)   (873)   9,324    (873)
1 Mar    14    (43)   (830)   (873)   (873)   8,494    (873)
1 Apr    14    (43)   (830)   (873)   (873)   7,664    (873)
1 May    14    (38)   (835)   (873)   (873)   6,829    (873)
1 Jun    14    (35)   (838)   (873)   (873)   5,991    (873)
 1 Jul   14    (30)   (844)   (873)   (873)   5,147    (873)
1 Aug    14    (26)   (847)   (873)   (873)   4,300    (873)
1 Sep    14    (22)   (851)   (873)   (873)   3,449    (873)
 1 Oct   14    (17)   (856)   (873)   (873)   2,593    (873)
1 Nov    14    (13)   (860)   (873)   (873)   1,733    (873)
1 Dec    14     (9)   (865)   (873)   (873)    869     (873)
 1 Jan   15     (4)   (869)   (873)   (873)     0      (873)
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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:16
posted:7/23/2011
language:English
pages:2
Description: Quarterly Mortgage Payment Formula document sample