Docstoc

Watson

Document Sample
Watson Powered By Docstoc
					New Zealand Applied Business Journal                                                Volume 1, Number 1, 2002



        A BACK-TO-BASICS APPROACH TO FINANCIAL MODELLING
  THE USE OF MATHEMATICAL MODELS AND SPREADSHEETS IN THE TEACHING OF FINANCIAL
 MATHEMATICS CAN LEAD TO A DEEPER UNDERSTANDING OF THE TIME VALUE OF MONEY AND
                           ENHANCE CRITICAL THINKING



                                               Peter Watson
                                     Department of Applied Mathematics
                                     Auckland University of Technology
                                          Auckland, New Zealand




            Abstract: Financial mathematics provides many examples of arithmetic, geometric
            and arithmetico-geometric progressions in action. In this paper a fragment of the
            material will be discussed and this will indicate the potential of the subject to explore
            mathematical models
            The examples explored and compared will be the table mortgage and the reducing
            mortgage. These are two popular methods used to construct a repayment schedule
            for a home mortgage so the real life context of the examples is immediately apparent.
            Some popular misconceptions regarding the way to compare them will be exposed
            and a more correct methodology proposed.


INTRODUCTION

To allow a comparison to be made the following example will be used throughout this paper.
Imagine you have applied for a loan of $120 000 to complete the purchase of a property and
you are offered the choice of a table mortgage or a reducing mortgage. Each loan will have
an interest rate of 9% pa compounded semi-annually and the loan is for 25 years.

The table mortgage has a timeline with the following features.



     $120 000         R         R        R                                                              R




             0        1         2        3…                                                             50



                                                 j2   0.09
                                    Where i =       =      = 0.045
                                                 2      2



The formula to calculate the regular payment can be found in any financial mathematics
textbook



                                                        1
Volume 1, Number 1, 2002                                         New Zealand Applied Business Journal


                                                P
                              R       =
                                               an i

Where P is the principal (P = $120 000), n is the number of periods (n = 50), and i is the
period interest rate (i = 0.045).

And

                                              1  (1  i )  n
                               an i   =
                                                    i

                                              1  1.04550
                                      =
                                                 0.045

                                      =       19.76200778

So                            R       =       $6072.257503

                                      =       $6072.26

                                              1  (1  i )  n
The proof of the formula     an i     =                        is surprisingly straightforward so
                                                    i
the mathematics involved will not obscure the model that is being solved. Appendix 1 gives
an explanation of the proof from first principles based on the concept of a geometric
progression (GP). A finance course tends to focus on the use of a formula to solve problems
whereas in a financial mathematics course the derivation of the formulae is also a feature.
The way the derivation is developed, it is submitted, leads towards a deeper understanding of
the time value of money and makes the concept of replacing a stream of payments by a single
payment on a particular day more obvious.

Students need to come to realise the effect of this formula. One important feature is the 50
payments of $6072.26 in the example are replaced with the single payment of $120 000 on
day 1 (period 0).

The spreadsheet provides an excellent tool to show the calculations for a table mortgage.

Appendix 2 shows an example of a spreadsheet that is designed to show this. In an
introductory course this might be as far as you would go. A usual question that arises is to
enquire as to how much interest is paid. You often find the following procedure followed.

To find the total interest paid it is a simple matter to autosum the column of interest
payments. Similarly it is easy to verify this is right by finding the sum of the payments and
subtracting the original principal. This is so easy to do that the verification might fool some
people. However maybe the solution of the equation of value (see appendix 1) might alert
students to the fallacy of what has just been done.




                                                2
New Zealand Applied Business Journal                                      Volume 1, Number 1, 2002


Before this is taken any further the reducing mortgage is now introduced and developed to
the same point.

The reducing loan is a loan where the principal is reduced by the same amount each period
and the interest owed is paid with each principal payment. You either state the size of each
principal payment which determines the number of payments or you state the number of
payments that will be made and calculate the size of each payment. The loan is called a
reducing loan because the amount paid each period reduces over time.

A reducing mortgage is a reducing loan where the loan is secured with a mortgage over a
property. This is a popular method of financing property deals in New Zealand.

                                                                                                 P
For a loan with a principal of P the size of each principal payment is found by calculating
                                                                                                 n
where n is the number of payments.

Again a spreadsheet can show the calculations for a reducing mortgage. Appendix 3 shows
an example of an Excel spreadsheet that is designed to show this. It can be seen that both this
spreadsheet and the spreadsheet in appendix 2 are created to be as similar in shape as possible
to show the way the calculations produce different answers.

The autosum button can in the same way be used to find      t  r   where tr is the interest at step
r.



A useful exercise in summing series can be developed here because it can be shown that tr is
                                                                                   Pi
an arithmetic progression (AP) with a first term of Pi and a common difference of      .
                                                                                   n

                P
The result is     (n  1) i             (See appendix 4 for a proof.)
                2

This is very easy to calculate and to verify that it gives the same value as        t r   from the
spreadsheet.

Again the result can be verified as    Payments  P
Comparing the Table Mortgage and the Reducing Mortgage

Looking at the two spreadsheets now it is easy to see that the sum of the interest payments for
the table mortgage is greater than the sum of the interest payments for the reducing mortgage.
Does this lead to the conclusion that the table mortgage is more expensive than the reducing
mortgage when the interest rate is the same? This calculation is often performed to show the
table mortgage is more expensive and an accompanying argument might go like this.
Because the table mortgage results in a smaller quantum of the principal being paid in the
earlier stages of the loan’s repayment the principal will diminish more slowly in the


                                                 3
Volume 1, Number 1, 2002                                  New Zealand Applied Business Journal


beginning and more rapidly towards the end of the loan period hence the higher interest.
While the argument is correct the addition of the interest payments is not appropriate. The
addition of different sums at different time periods shows a lack of appreciation of the time
value of money. It is therefore more appropriate to find the present value (PV) of the stream
of interest payments and to compare those.

In appendix 5 the table mortgage schedule in appendix 2 has been extended to provide 3 extra
columns. At each step the present value of the interest, the principal outstanding and the
payment has been calculated. This is achieved by multiplying the corresponding value in row
r by (1  i )  r .

The patterns in the data are immediately apparent however the most interesting pattern is the
value in the principal column. The value is clearly independent of r.

The Net Present Value (NPV) of the interest in cell I2 ($87 834.57) is obtained by summing
the present values in column G (G11:G60). Similarly I3 = Sum (H11:H60) and I4 = Sum
(I11:I60).

In appendix 6 the reducing mortgage schedule in appendix 3 has been similarly extended and
it is immediately apparent that the NPV of the interest payments ($72 571.18) is less than the
NPV for the table mortgage and this is the appropriate comparison to make.

While it might be obvious that the NPV of the payments in column I is equal to the principal

($120 000) in each case the spreadsheets provide a useful and spectacular verification of this
fact.

Deriving the formulae for the Net Present Value

The derivation of the results for the various NPV values can verify the numbers obtained and
lead to an algebraic comparison. The analysis will also lead to a deeper understanding of the
concepts involved.

The following table summarises the results of the various calculations.




                                               4
New Zealand Applied Business Journal                                                      Volume 1, Number 1, 2002




At step r                          Table Mortgage                              Reducing Mortgage
Outstanding Principal              P(1  i)  Rsr i
                                              r
                                                                                Pr
                                                                                        P
                                                                                        n
PV of the Principal Repaid         ( R  Pi )(1  i ) 1                        P
                                                                                   (1  i) r
                                                                                n
PV of the Payment                  R (1  i )  r                                P Pi                 
                                                                                 n  n 1  n  r   (1  i )
                                                                                                                  r

                                                                                                      
PV of the Interest                 R(1  i )  r  ( R  Pi )(1  i ) 1                     P               r
                                                                                 P  (r  1) n  i  (1  i )
                                                                                               
For n Payments
NPV of the           Principal     n( R  Pi )(1  i ) 1                       P
Repaid                                                                            a
                                                                                n ni
NPV of the Repayments              P                                           P
                                                                  1
NPV of the Interest                P  n( R  Pi )(1  i )                            P
                                                                                P      a
                                                                                      n ni


                         (1  i ) r  1                                    1  (1  i )  r
Where sr i      =                             and          ar i        =
                               i                                                 i

Proofs of the key formulae in this table can be found in appendices 7 and 8. The proofs of
the NPV formulae generally involve the summation of a GP. This type of summation might
be found in any introductory algebra course. The same cannot be said for the arithmetico-
geometric progression, which is a progression that is simultaneously arithmetic and
geometric. The way this is treated can be seen in appendix 8 and to find a need to solve a
problem of this type in a real life context provides a worthwhile extension to the material.

Scientific calculators are now a basic requirement for students so all have access to the
functions required in a finance course. While all students should develop facility with the use
of their calculator a financial mathematics course will provide many opportunities for them to
do so. Many finance textbooks still provide compound interest tables and annuity tables and
show students how to use them. [Adams et al, Brealey and Myers, Croucher, Francis and
Taylor, Gitman, McLean and Stephens, Shim and Siegel, Van Horne]. A financial
mathematics course on the other hand can treat the generation of these tables as an exercise.
A smaller number of textbook show the formulae in action with worked examples and
problems [Knox et al, McLean and Stephens, Shannon, Waters, Zima and Brown]. Some
show the formulae and give worked examples based on the tables [Brealey and Myers,
Croucher, Gitman, Shim and Siegel, Van Horne]. Burton et al take a different approach,
neither tables nor formulae are mentioned with every problem being tackled from first
principles. A text with a financial mathematics approach often includes the tables for
completeness but more often than not shows worked examples that focus on the formulae
[Adams et al, McCutcheon and Scott].




                                                            5
Volume 1, Number 1, 2002                                  New Zealand Applied Business Journal




CONCLUSION

A financial mathematics course provides the opportunity to explore not only the practical
applications of financial problems in a business context but also the underlying mathematical
formulae and how they are derived. The development of the mathematical models and the
building of the spreadsheets using an algorithmic approach lead to a deeper understanding of
the processes involved. During the earlier stages of a financial mathematics course
deterministic models such as those touched on in this paper provide a practical application of
the algebraic processes under the general category of “progressions”. An appreciation of the
concepts involved can lead to an ability to develop proofs outside those found in standard
discussions of the subject.




                                              6
New Zealand Applied Business Journal                                               Volume 1, Number 1, 2002




REFERENCES
Adams, A.T., Bloomfield, D.S.F., Booth, P.M., England, P.D. (1993). Investment Mathematics and Statistics.
     London: Graham & Trotman

Brealey, R. and Myers, S. (1984). Principles of Corporate Finance (2nd ed.). Singapore: McGraw-Hill

Burton, G., Carrol, G. and Wall, S. (1999). Quantitative Methods for Business and Economics. Harlow: Addison
      Wesley Longman

Croucher, J.S. (1998). Introductory Mathematics and Statistics for Business (3rd ed.). Australia: McGraw-Hill

Francis, J. C., Taylor, R. W. (2000). Investments. New York: McGraw-Hill

Gitman, L. J. (1997). Principles of Managerial Finance (8th ed.). Massachusetts: Addison-Wesley Longman

Knox, D.M., Zima, P., Brown, R.L. (1997). Mathematics of Finance. Australia: McGraw-Hill

McCutcheon, J.J., Scott, W.F. (1986). An Introduction to the Mathematics of Finance. Oxford: Butterworth-
     Heinemann

McLean, A., Stephens, B. (1996). Business Mathematics and Statistics. Melbourne: Addison Wesley Longman

Shannon, J. (1995). Mathematics for Business, Economics and Finance. Brisbane: John Wiley & Sons

Shim, J. K., Siegel, J. G. (1998). Financial Management (2nd ed.). New York: McGraw-Hill

Van Horne, J.C. (1989). Financial Management and Policy (8th ed.). London: Prentice Hall International

Waters, D. (1997). Quantitative Methods for Business (2nd ed.). Harlow: Addison Wesley Longman

Zima, P., Brown, R.L. (1996). Mathematics of Finance. New York: McGraw-Hill




                                                       7
Volume 1, Number 1, 2002                                                    New Zealand Applied Business Journal




APPENDIX 1: DERIVING THE PRESENT VALUE ANNUITY FACTOR FROM FIRST
PRINCIPLES

                                      1  (1  i )  n
A proof of the formula an i            =               from first principles provides a useful
                                            i
insight into the use of geometric progressions (GPs) in financial mathematics.



The present value (PV) of the payment R at period r = R(1  i ) r . There are n payments so



                                                   n
PV of the n payments                   =          R(1  i )
                                                  r 1
                                                                r




                                       =         R(1  i )1  R(1  i )2  ...  R(1  i ) n

                                       =         R (1  i )1  (1  i )2  ...  (1  i ) n 
                                                                                               

The series in the square brackets is a GP with t1 = (1  i )1 and common ratio r = (1  i )1



                                1 rn
From the formula Sn = t1
                                1 r

                             1  (1  i ) n
Sn     =        (1  i )1
                             1  (1  i )1

                  1 1  (1  i ) n
       =             
                1  i 1  (1  i )1

                       1  (1  i ) n
       =
                (1  i )  (1  i )(1  i )1

                1  (1  i ) n
       =
                  1 i 1

                1  (1  i ) n
       =
                      i

                                      1  (1  i ) n
So PV of the n payments is R
                                            i




                                                            8
New Zealand Applied Business Journal                                                                       Volume 1, Number 1, 2002




APPENDIX 2: THE TABLE MORTGAGE CALCULATOR

     Table Loan and Table Mortgage Calculator


     Principal                    $120,000.00               Interest
     Number of Payments                   50                Added           $183,612.88
     Monthly Payment                $6,072.26               Calculated      $183,612.88
     Period Interest Rate              4.50%


                    Number          Principal                                                  Principal
                 of Periods      Outstanding                    Principal       Monthly     Outstanding
                            n    at Beginning    Interest Repayment             Payment          at End
                            1     $120,000.00   $5,400.00        $672.26        $6,072.26   $119,327.74
                            2     $119,327.74   $5,369.75        $702.51        $6,072.26   $118,625.23
                            3     $118,625.23   $5,338.14        $734.12        $6,072.26   $117,891.11
                            4     $117,891.11   $5,305.10        $767.16        $6,072.26   $117,123.95
                            5     $117,123.95   $5,270.58        $801.68        $6,072.26   $116,322.27
                            6     $116,322.27   $5,234.50        $837.76        $6,072.26   $115,484.52
                            7     $115,484.52   $5,196.80        $875.45        $6,072.26   $114,609.07
                            8     $114,609.07   $5,157.41        $914.85        $6,072.26   $113,694.22
                            9     $113,694.22   $5,116.24        $956.02        $6,072.26   $112,738.20
                            10    $112,738.20   $5,073.22        $999.04        $6,072.26   $111,739.16
                            11    $111,739.16   $5,028.26      $1,044.00        $6,072.26   $110,695.16
                            12    $110,695.16   $4,981.28      $1,090.98        $6,072.26   $109,604.19
                            13    $109,604.19   $4,932.19      $1,140.07        $6,072.26   $108,464.12
                            14    $108,464.12   $4,880.89      $1,191.37        $6,072.26   $107,272.75
                            15    $107,272.75   $4,827.27      $1,244.98        $6,072.26   $106,027.76
                            16    $106,027.76   $4,771.25      $1,301.01        $6,072.26   $104,726.76
                            17    $104,726.76   $4,712.70      $1,359.55        $6,072.26   $103,367.20
                            18    $103,367.20   $4,651.52      $1,420.73        $6,072.26   $101,946.47
                            19    $101,946.47   $4,587.59      $1,484.67        $6,072.26   $100,461.80
                            20    $100,461.80   $4,520.78      $1,551.48        $6,072.26    $98,910.33
                            21     $98,910.33   $4,450.96      $1,621.29        $6,072.26    $97,289.03
                            22     $97,289.03   $4,378.01      $1,694.25        $6,072.26    $95,594.78
                            23     $95,594.78   $4,301.77      $1,770.49        $6,072.26    $93,824.29
                            24     $93,824.29   $4,222.09      $1,850.16        $6,072.26    $91,974.13
                            25     $91,974.13   $4,138.84      $1,933.42        $6,072.26    $90,040.70
                            26     $90,040.70   $4,051.83      $2,020.43        $6,072.26    $88,020.28
                            27     $88,020.28   $3,960.91      $2,111.35        $6,072.26    $85,908.93
                            28     $85,908.93   $3,865.90      $2,206.36        $6,072.26    $83,702.58
                            29     $83,702.58   $3,766.62      $2,305.64        $6,072.26    $81,396.94
                            30     $81,396.94   $3,662.86      $2,409.40        $6,072.26    $78,987.54
                            31     $78,987.54   $3,554.44      $2,517.82        $6,072.26    $76,469.72
                            32     $76,469.72   $3,441.14      $2,631.12        $6,072.26    $73,838.60
                            33     $73,838.60   $3,322.74      $2,749.52        $6,072.26    $71,089.08
                            34     $71,089.08   $3,199.01      $2,873.25        $6,072.26    $68,215.83
                            35     $68,215.83   $3,069.71      $3,002.55        $6,072.26    $65,213.29
                            36     $65,213.29   $2,934.60      $3,137.66        $6,072.26    $62,075.63
                            37     $62,075.63   $2,793.40      $3,278.85        $6,072.26    $58,796.77
                            38     $58,796.77   $2,645.85      $3,426.40        $6,072.26    $55,370.37
                            39     $55,370.37   $2,491.67      $3,580.59        $6,072.26    $51,789.78
                            40     $51,789.78   $2,330.54      $3,741.72        $6,072.26    $48,048.06
                            41     $48,048.06   $2,162.16      $3,910.09        $6,072.26    $44,137.97
                            42     $44,137.97   $1,986.21      $4,086.05        $6,072.26    $40,051.92
                            43     $40,051.92   $1,802.34      $4,269.92        $6,072.26    $35,782.00
                            44     $35,782.00   $1,610.19      $4,462.07        $6,072.26    $31,319.93
                            45     $31,319.93   $1,409.40      $4,662.86        $6,072.26    $26,657.07
                            46     $26,657.07   $1,199.57      $4,872.69        $6,072.26    $21,784.38
                            47     $21,784.38    $980.30       $5,091.96        $6,072.26    $16,692.42
                            48     $16,692.42    $751.16       $5,321.10        $6,072.26    $11,371.32
                            49     $11,371.32    $511.71       $5,560.55        $6,072.26     $5,810.77
                            50      $5,810.77    $261.48       $5,810.77        $6,072.26        -$0.00




                                                                            9
Volume 1, Number 1, 2002                                                                New Zealand Applied Business Journal


APPENDIX 3: THE REDUCING MORTGAGE CALCULATOR

Reducing Loan and Reducing Mortgage Calculator


Principal                    $120,000.00               Interest
Number of Payments                   50                Added           $137,700.00
Principal Repayment            $2,400.00               Calculated      $137,700.00
Period Interest Rate              4.50%


                 Number        Principal                                                Principal
               of Periods   Outstanding                    Principal         Monthly Outstanding
                       n    at Beginning    Interest    Repayment           Payment       at End
                       1       $120,000    $5,400.00      $2,400.00         $7,800.00   $117,600
                       2       $117,600    $5,292.00      $2,400.00         $7,692.00   $115,200
                       3       $115,200    $5,184.00      $2,400.00         $7,584.00   $112,800
                       4       $112,800    $5,076.00      $2,400.00         $7,476.00   $110,400
                       5       $110,400    $4,968.00      $2,400.00         $7,368.00   $108,000
                       6       $108,000    $4,860.00      $2,400.00         $7,260.00   $105,600
                       7       $105,600    $4,752.00      $2,400.00         $7,152.00   $103,200
                       8       $103,200    $4,644.00      $2,400.00         $7,044.00   $100,800
                       9       $100,800    $4,536.00      $2,400.00         $6,936.00    $98,400
                       10       $98,400    $4,428.00      $2,400.00         $6,828.00    $96,000
                       11       $96,000    $4,320.00      $2,400.00         $6,720.00    $93,600
                       12       $93,600    $4,212.00      $2,400.00         $6,612.00    $91,200
                       13       $91,200    $4,104.00      $2,400.00         $6,504.00    $88,800
                       14       $88,800    $3,996.00      $2,400.00         $6,396.00    $86,400
                       15       $86,400    $3,888.00      $2,400.00         $6,288.00    $84,000
                       16       $84,000    $3,780.00      $2,400.00         $6,180.00    $81,600
                       17       $81,600    $3,672.00      $2,400.00         $6,072.00    $79,200
                       18       $79,200    $3,564.00      $2,400.00         $5,964.00    $76,800
                       19       $76,800    $3,456.00      $2,400.00         $5,856.00    $74,400
                       20       $74,400    $3,348.00      $2,400.00         $5,748.00    $72,000
                       21       $72,000    $3,240.00      $2,400.00         $5,640.00    $69,600
                       22       $69,600    $3,132.00      $2,400.00         $5,532.00    $67,200
                       23       $67,200    $3,024.00      $2,400.00         $5,424.00    $64,800
                       24       $64,800    $2,916.00      $2,400.00         $5,316.00    $62,400
                       25       $62,400    $2,808.00      $2,400.00         $5,208.00    $60,000
                       26       $60,000    $2,700.00      $2,400.00         $5,100.00    $57,600
                       27       $57,600    $2,592.00      $2,400.00         $4,992.00    $55,200
                       28       $55,200    $2,484.00      $2,400.00         $4,884.00    $52,800
                       29       $52,800    $2,376.00      $2,400.00         $4,776.00    $50,400
                       30       $50,400    $2,268.00      $2,400.00         $4,668.00    $48,000
                       31       $48,000    $2,160.00      $2,400.00         $4,560.00    $45,600
                       32       $45,600    $2,052.00      $2,400.00         $4,452.00    $43,200
                       33       $43,200    $1,944.00      $2,400.00         $4,344.00    $40,800
                       34       $40,800    $1,836.00      $2,400.00         $4,236.00    $38,400
                       35       $38,400    $1,728.00      $2,400.00         $4,128.00    $36,000
                       36       $36,000    $1,620.00      $2,400.00         $4,020.00    $33,600
                       37       $33,600    $1,512.00      $2,400.00         $3,912.00    $31,200
                       38       $31,200    $1,404.00      $2,400.00         $3,804.00    $28,800
                       39       $28,800    $1,296.00      $2,400.00         $3,696.00    $26,400
                       40       $26,400    $1,188.00      $2,400.00         $3,588.00    $24,000
                       41       $24,000    $1,080.00      $2,400.00         $3,480.00    $21,600
                       42       $21,600     $972.00       $2,400.00         $3,372.00    $19,200
                       43       $19,200     $864.00       $2,400.00         $3,264.00    $16,800
                       44       $16,800     $756.00       $2,400.00         $3,156.00    $14,400
                       45       $14,400     $648.00       $2,400.00         $3,048.00    $12,000
                       46       $12,000     $540.00       $2,400.00         $2,940.00     $9,600
                       47        $9,600     $432.00       $2,400.00         $2,832.00     $7,200
                       48        $7,200     $324.00       $2,400.00         $2,724.00     $4,800
                       49        $4,800     $216.00       $2,400.00         $2,616.00     $2,400
                       50        $2,400     $108.00       $2,400.00         $2,508.00         $0




                                                                       10
New Zealand Applied Business Journal                                            Volume 1, Number 1, 2002




APPENDIX 4: FINDING THE SUM OF THE INTEREST PAYMENTS FOR A REDUCING
MORTGAGE



The formula to calculate the total interest is
                                                       P
                                                         n  1 i                            .......... (1)
                                                       2

Where P is the principal, n is the number of periods and i is the period interest rate

The derivation of this formula from first principles follows

Let the principal be P, the number of periods be n and the period interest rate be i.

                                                                                          P
Let the regular equal payment by which the principal is reduced each period be
                                                                                          n

Let tr be the interest paid in period r.



                                t1          =           Pi                                   .......... (2)

                                                            P
                                t2          =           P  n   i
                                                              

                                                              P
                                t3          =           P  2 n   i
                                                                

                                .....................................

                                .....................................

                                .....................................

                                                                     P
                                tr          =            P  (r  1) n   i
                                                                       

                                .....................................

                                .....................................

                                .....................................

                                                                     P
                                tn          =            P  (n  1) n   I
                                                                       

                                                                                 P
This is an arithmetic progression (AP) with a common difference of                i         …….. (3)
                                                                                 n


                                                         11
Volume 1, Number 1, 2002                                     New Zealand Applied Business Journal



To find the total interest use sn    =
                                            n
                                              2t1  n  1d                          .......... (4)
                                            2

Where          n is the number of periods

               t1 is P  i                           from (2)

                    P
               d=          xi        from (3)
                     n

Substitute these into (4)

                                            n                  P 
                                sn   =         2P  i  n  1    i
                                            2
                                                                n  

                                            n             P
                                     =       2P  n  1 n   i
                                            2               

                                            n          P P
                                     =       2 P  n  n  n   i
                                            2                

                                            n          P
                                     =       2 P  P  n   i
                                            2            

                                            n    P
                                     =         P  i
                                            2
                                                 n

                                            n  P  n  1
                                     =                     i
                                              2  n 
                                                        


                                     =
                                            P
                                              n  1 i
                                            2


                                Or   =
                                            P
                                              n  1i                   which is (1)
                                            2




                                                12
New Zealand Applied Business Journal                                                                             Volume 1, Number 1, 2002




APPENDIX 5: THE COMPLETED TABLE MORTGAGE CALCULATOR WITH NPV
CALCULATIONS
   Table Loan and Table Mortgage Calculator
                                                                                                       NPV              Interest        $87,834.57
   Principal                    $120,000.00               Interest                                                      Principal       $32,165.43
   Number of Payments                   50                Added           $183,612.88                                   Payment        $120,000.00
   Monthly Payment                $6,072.26               Calculated      $183,612.88                  Check            I+P=           $120,000.00
   Period Interest Rate              4.50%                NPV              $87,834.57
                                                                                                             Present        Present       Present
                  Number          Principal                                                Principal           Value        Value of      Value of
                of Periods     Outstanding                    Principal      Periodic   Outstanding of Interest @ Principal @          Payment @
                          n    at Beginning    Interest    Repayment        Payment          at End            4.50%           4.50%        4.50%
                          1     $120,000.00   $5,400.00         $672.26     $6,072.26   $119,327.74      $5,167.46           $643.31     $5,810.77
                          2     $119,327.74   $5,369.75         $702.51     $6,072.26   $118,625.23      $4,917.24           $643.31     $5,560.55
                          3     $118,625.23   $5,338.14         $734.12     $6,072.26   $117,891.11      $4,677.79           $643.31     $5,321.10
                          4     $117,891.11   $5,305.10         $767.16     $6,072.26   $117,123.95      $4,448.65           $643.31     $5,091.96
                          5     $117,123.95   $5,270.58         $801.68     $6,072.26   $116,322.27      $4,229.38           $643.31     $4,872.69
                          6     $116,322.27   $5,234.50         $837.76     $6,072.26   $115,484.52      $4,019.55           $643.31     $4,662.86
                          7     $115,484.52   $5,196.80         $875.45     $6,072.26   $114,609.07      $3,818.76           $643.31     $4,462.07
                          8     $114,609.07   $5,157.41         $914.85     $6,072.26   $113,694.22      $3,626.61           $643.31     $4,269.92
                          9     $113,694.22   $5,116.24         $956.02     $6,072.26   $112,738.20      $3,442.74           $643.31     $4,086.05
                          10    $112,738.20   $5,073.22         $999.04     $6,072.26   $111,739.16      $3,266.79           $643.31     $3,910.09
                          11    $111,739.16   $5,028.26      $1,044.00      $6,072.26   $110,695.16      $3,098.41           $643.31     $3,741.72
                          12    $110,695.16   $4,981.28      $1,090.98      $6,072.26   $109,604.19      $2,937.28           $643.31     $3,580.59
                          13    $109,604.19   $4,932.19      $1,140.07      $6,072.26   $108,464.12      $2,783.09           $643.31     $3,426.40
                          14    $108,464.12   $4,880.89      $1,191.37      $6,072.26   $107,272.75      $2,635.55           $643.31     $3,278.85
                          15    $107,272.75   $4,827.27      $1,244.98      $6,072.26   $106,027.76      $2,494.35           $643.31     $3,137.66
                          16    $106,027.76   $4,771.25      $1,301.01      $6,072.26   $104,726.76      $2,359.24           $643.31     $3,002.55
                          17    $104,726.76   $4,712.70      $1,359.55      $6,072.26   $103,367.20      $2,229.94           $643.31     $2,873.25
                          18    $103,367.20   $4,651.52      $1,420.73      $6,072.26   $101,946.47      $2,106.21           $643.31     $2,749.52
                          19    $101,946.47   $4,587.59      $1,484.67      $6,072.26   $100,461.80      $1,987.81           $643.31     $2,631.12
                          20    $100,461.80   $4,520.78      $1,551.48      $6,072.26    $98,910.33      $1,874.51           $643.31     $2,517.82
                          21     $98,910.33   $4,450.96      $1,621.29      $6,072.26    $97,289.03      $1,766.09           $643.31     $2,409.40
                          22     $97,289.03   $4,378.01      $1,694.25      $6,072.26    $95,594.78      $1,662.33           $643.31     $2,305.64
                          23     $95,594.78   $4,301.77      $1,770.49      $6,072.26    $93,824.29      $1,563.05           $643.31     $2,206.36
                          24     $93,824.29   $4,222.09      $1,850.16      $6,072.26    $91,974.13      $1,468.04           $643.31     $2,111.35
                          25     $91,974.13   $4,138.84      $1,933.42      $6,072.26    $90,040.70      $1,377.12           $643.31     $2,020.43
                          26     $90,040.70   $4,051.83      $2,020.43      $6,072.26    $88,020.28      $1,290.11           $643.31     $1,933.42
                          27     $88,020.28   $3,960.91      $2,111.35      $6,072.26    $85,908.93      $1,206.86           $643.31     $1,850.16
                          28     $85,908.93   $3,865.90      $2,206.36      $6,072.26    $83,702.58      $1,127.18           $643.31     $1,770.49
                          29     $83,702.58   $3,766.62      $2,305.64      $6,072.26    $81,396.94      $1,050.94           $643.31     $1,694.25
                          30     $81,396.94   $3,662.86      $2,409.40      $6,072.26    $78,987.54          $977.98         $643.31     $1,621.29
                          31     $78,987.54   $3,554.44      $2,517.82      $6,072.26    $76,469.72          $908.17         $643.31     $1,551.48
                          32     $76,469.72   $3,441.14      $2,631.12      $6,072.26    $73,838.60          $841.36         $643.31     $1,484.67
                          33     $73,838.60   $3,322.74      $2,749.52      $6,072.26    $71,089.08          $777.42         $643.31     $1,420.73
                          34     $71,089.08   $3,199.01      $2,873.25      $6,072.26    $68,215.83          $716.24         $643.31     $1,359.55
                          35     $68,215.83   $3,069.71      $3,002.55      $6,072.26    $65,213.29          $657.70         $643.31     $1,301.01
                          36     $65,213.29   $2,934.60      $3,137.66      $6,072.26    $62,075.63          $601.68         $643.31     $1,244.98
                          37     $62,075.63   $2,793.40      $3,278.85      $6,072.26    $58,796.77          $548.06         $643.31     $1,191.37
                          38     $58,796.77   $2,645.85      $3,426.40      $6,072.26    $55,370.37          $496.76         $643.31     $1,140.07
                          39     $55,370.37   $2,491.67      $3,580.59      $6,072.26    $51,789.78          $447.67         $643.31     $1,090.98
                          40     $51,789.78   $2,330.54      $3,741.72      $6,072.26    $48,048.06          $400.69         $643.31     $1,044.00
                          41     $48,048.06   $2,162.16      $3,910.09      $6,072.26    $44,137.97          $355.73         $643.31      $999.04
                          42     $44,137.97   $1,986.21      $4,086.05      $6,072.26    $40,051.92          $312.71         $643.31      $956.02
                          43     $40,051.92   $1,802.34      $4,269.92      $6,072.26    $35,782.00          $271.54         $643.31      $914.85
                          44     $35,782.00   $1,610.19      $4,462.07      $6,072.26    $31,319.93          $232.15         $643.31      $875.45
                          45     $31,319.93   $1,409.40      $4,662.86      $6,072.26    $26,657.07          $194.45         $643.31      $837.76
                          46     $26,657.07   $1,199.57      $4,872.69      $6,072.26    $21,784.38          $158.37         $643.31      $801.68
                          47     $21,784.38    $980.30       $5,091.96      $6,072.26    $16,692.42          $123.85         $643.31      $767.16
                          48     $16,692.42    $751.16       $5,321.10      $6,072.26    $11,371.32            $90.81        $643.31      $734.12
                          49     $11,371.32    $511.71       $5,560.55      $6,072.26     $5,810.77            $59.20        $643.31      $702.51
                          50      $5,810.77    $261.48       $5,810.77      $6,072.26         -$0.00           $28.95        $643.31      $672.26




                                                                          13
 Volume 1, Number 1, 2002                                                                           New Zealand Applied Business Journal




 APPENDIX 6: THE COMPLETED REDUCING MORTGAGE CALCULATOR WITH NPV
 CALCULATIONS
Reducing Loan and Reducing Mortgage Calculator
                                                                                                    NPV              Interest               $72,571.18
Principal                    $120,000.00               Interest                                                      Principal              $47,428.82
Number of Payments                   50                Added           $137,700.00                                   Payment               $120,000.00
Principal Repayment            $2,400.00               Calculated      $137,700.00                  Check            I+P=                  $120,000.00
Period Interest Rate              4.50%                NPV              $72,571.18
                                                                                                          Present               Present       Present
               Number          Principal                                                Principal         Value of              Value of      Value of
            of Periods      Outstanding                    Principal      Periodic   Outstanding      Interest @          Principal @      Payment @
                       n    at Beginning    Interest Repayment           Payment          at End            4.50%                 4.50%         4.50%
                       1       $120,000    $5,400.00      $2,400.00      $7,800.00      $117,600      $5,167.46             $2,296.65        $7,464.11
                       2       $117,600    $5,292.00      $2,400.00      $7,692.00      $115,200      $4,846.04             $2,197.75        $7,043.79
                       3       $115,200    $5,184.00      $2,400.00      $7,584.00      $112,800      $4,542.72             $2,103.11        $6,645.83
                       4       $112,800    $5,076.00      $2,400.00      $7,476.00      $110,400      $4,256.54             $2,012.55        $6,269.08
                       5       $110,400    $4,968.00      $2,400.00      $7,368.00      $108,000      $3,986.58             $1,925.88        $5,912.46
                       6       $108,000    $4,860.00      $2,400.00      $7,260.00      $105,600      $3,731.97             $1,842.95        $5,574.92
                       7       $105,600    $4,752.00      $2,400.00      $7,152.00      $103,200      $3,491.90             $1,763.59        $5,255.49
                       8       $103,200    $4,644.00      $2,400.00      $7,044.00      $100,800      $3,265.59             $1,687.64        $4,953.24
                       9       $100,800    $4,536.00      $2,400.00      $6,936.00       $98,400      $3,052.29             $1,614.97        $4,667.27
                       10       $98,400    $4,428.00      $2,400.00      $6,828.00       $96,000      $2,851.31             $1,545.43        $4,396.74
                       11       $96,000    $4,320.00      $2,400.00      $6,720.00       $93,600      $2,661.98             $1,478.88        $4,140.86
                       12       $93,600    $4,212.00      $2,400.00      $6,612.00       $91,200      $2,483.66             $1,415.19        $3,898.86
                       13       $91,200    $4,104.00      $2,400.00      $6,504.00       $88,800      $2,315.77             $1,354.25        $3,670.02
                       14       $88,800    $3,996.00      $2,400.00      $6,396.00       $86,400      $2,157.73             $1,295.93        $3,453.67
                       15       $86,400    $3,888.00      $2,400.00      $6,288.00       $84,000      $2,009.01             $1,240.13        $3,249.14
                       16       $84,000    $3,780.00      $2,400.00      $6,180.00       $81,600      $1,869.09             $1,186.73        $3,055.82
                       17       $81,600    $3,672.00      $2,400.00      $6,072.00       $79,200      $1,737.50             $1,135.62        $2,873.13
                       18       $79,200    $3,564.00      $2,400.00      $5,964.00       $76,800      $1,613.78             $1,086.72        $2,700.50
                       19       $76,800    $3,456.00      $2,400.00      $5,856.00       $74,400      $1,497.49             $1,039.92        $2,537.42
                       20       $74,400    $3,348.00      $2,400.00      $5,748.00       $72,000      $1,388.22                  $995.14     $2,383.37
                       21       $72,000    $3,240.00      $2,400.00      $5,640.00       $69,600      $1,285.59                  $952.29     $2,237.88
                       22       $69,600    $3,132.00      $2,400.00      $5,532.00       $67,200      $1,189.22                  $911.28     $2,100.51
                       23       $67,200    $3,024.00      $2,400.00      $5,424.00       $64,800      $1,098.77                  $872.04     $1,970.81
                       24       $64,800    $2,916.00      $2,400.00      $5,316.00       $62,400      $1,013.90                  $834.49     $1,848.39
                       25       $62,400    $2,808.00      $2,400.00      $5,208.00       $60,000          $934.31                $798.55     $1,732.86
                       26       $60,000    $2,700.00      $2,400.00      $5,100.00       $57,600          $859.69                $764.17     $1,623.85
                       27       $57,600    $2,592.00      $2,400.00      $4,992.00       $55,200          $789.76                $731.26     $1,521.02
                       28       $55,200    $2,484.00      $2,400.00      $4,884.00       $52,800          $724.26                $699.77     $1,424.03
                       29       $52,800    $2,376.00      $2,400.00      $4,776.00       $50,400          $662.94                $669.64     $1,332.58
                       30       $50,400    $2,268.00      $2,400.00      $4,668.00       $48,000          $605.56                $640.80     $1,246.36
                       31       $48,000    $2,160.00      $2,400.00      $4,560.00       $45,600          $551.89                $613.21     $1,165.09
                       32       $45,600    $2,052.00      $2,400.00      $4,452.00       $43,200          $501.71                $586.80     $1,088.51
                       33       $43,200    $1,944.00      $2,400.00      $4,344.00       $40,800          $454.84                $561.53     $1,016.37
                       34       $40,800    $1,836.00      $2,400.00      $4,236.00       $38,400          $411.07                $537.35      $948.42
                       35       $38,400    $1,728.00      $2,400.00      $4,128.00       $36,000          $370.23                $514.21      $884.44
                       36       $36,000    $1,620.00      $2,400.00      $4,020.00       $33,600          $332.15                $492.07      $824.21
                       37       $33,600    $1,512.00      $2,400.00      $3,912.00       $31,200          $296.65                $470.88      $767.53
                       38       $31,200    $1,404.00      $2,400.00      $3,804.00       $28,800          $263.60                $450.60      $714.20
                       39       $28,800    $1,296.00      $2,400.00      $3,696.00       $26,400          $232.85                $431.20      $664.04
                       40       $26,400    $1,188.00      $2,400.00      $3,588.00       $24,000          $204.25                $412.63      $616.88
                       41       $24,000    $1,080.00      $2,400.00      $3,480.00       $21,600          $177.69                $394.86      $572.55
                       42       $21,600     $972.00       $2,400.00      $3,372.00       $19,200          $153.03                $377.86      $530.89
                       43       $19,200     $864.00       $2,400.00      $3,264.00       $16,800          $130.17                $361.59      $491.76
                       44       $16,800     $756.00       $2,400.00      $3,156.00       $14,400          $108.99                $346.01      $455.01
                       45       $14,400     $648.00       $2,400.00      $3,048.00       $12,000            $89.40               $331.11      $420.52
                       46       $12,000     $540.00       $2,400.00      $2,940.00        $9,600            $71.29               $316.86      $388.15
                       47        $9,600     $432.00       $2,400.00      $2,832.00        $7,200            $54.58               $303.21      $357.79
                       48        $7,200     $324.00       $2,400.00      $2,724.00        $4,800            $39.17               $290.15      $329.33
                       49        $4,800     $216.00       $2,400.00      $2,616.00        $2,400            $24.99               $277.66      $302.65
                       50        $2,400     $108.00       $2,400.00      $2,508.00            $0            $11.96               $265.70      $277.66




                                                                               14
New Zealand Applied Business Journal                                             Volume 1, Number 1, 2002




APPENDIX 7: ALGEBRAIC VERIFICATION OF THE TABLE MORTGAGE FORMULAE

To prove that the present value of the principal repaid at step r+1 for a table mortgage
is ( R  Pi )(1  i ) 1 , which is independent of r. Hence the NPV of all principal repayments
is n( R  Pi )(1  i ) 1 .

At step r the outstanding principal is P(1  i)r  Rsr i i.e. the accumulated value of the
principal at the end of period r minus the accumulated value of the r payments of R made at
the end of each period.

At step r+1 the interest charged       =       P (1  i ) r  Rs   i
                                                                r i



The principal repaid at step r+1       =      R   P(1  i ) r  Rsr i   i
                                                                       

The present value of the principal repaid at step r+1

                                       =       R   P(1  i ) r  Rs   i   (1  i )  r 1
                                                                    r i   

                                       =                    
                                               R 1  is  Pi (1  i ) r   (1  i )  r 1
                                                       r i              

                                                         (1  i ) r  1                             r 1
                                       =       R 1  i                   Pi (1  i )   (1  i )
                                                                                         r

                                                               i                        

                                       =       R 1  (1  i)r  1  Pi(1  i)r   (1  i) r 1
                                                                                 

                                       =       R(1  i)r  Pi(1  i)r   (1  i) r 1
                                                                      

                                       =      ( R  Pi)(1  i)r   (1  i) r 1
                                                                

                                               R  Pi 1  i 
                                                                   r  r 1
                                       =

                                               R  Pi 1  i 
                                                                   1
                                       =

There are n such payments so the NPV of the n payments

                                              n  R  Pi 1  i 
                                                                        1
                                       =

For the model n = 50 so R = 6072.257503 (for verification purposes), P = 120 000, i = 0.045

                                              50   6072.257503  120000  0.045  1.045
                                                                                                                1
So              NPV                    =



                                               15
Volume 1, Number 1, 2002                                               New Zealand Applied Business Journal


                                      =      32165.43077

                                      =      $32 165.43                  As shown on the spreadsheet



To show the NPV of the payments is P

This is true by definition of an ordinary simple annuity

                                              R 1  i 
                                                           r
At step r      PV of R                =

                                               n

                                               R 1  i 
                                                                r
The sum of the n payments             =
                                              r 1


                                                     n
                                              R  1  i 
                                                                r
                                      =
                                                   r 1


                                      =       Ran i

                                      =      P

To prove the NPV of the interest payments is P  n  R  Pi 1  i 
                                                                                        1




It is clear that this formula is simply the difference between the NPV of the repayments
minus the NPV of the principal repaid

Looking at step r+1 the interest      =        P 1  i r  Rs   i
                                                               r i



And the PV of this interest           =        P 1  i r  Rs   i  1  i  r 1
                                                               r i



Following the simplification above it becomes

                                                                1  i   1  1  i  r 1
                                                                         r

                                                Pi 1  i   Ri                    
                                                           r
                                      =       
                                              
                                                                      i     
                                                                             

                                      =        Pi 1  i r  R 1  i r  R   1  i  r 1
                                                                              

                                              Pi 1  i                R 1  i                R 1  i 
                                                            r  r 1                  r  r 1                   r 1
                                      =

                                                            r 1
                                              R 1  i                  R  Pi 1  i 
                                                                                                   1
                                      =

Close inspection will show this has been rearranged to resemble the previous two present
value calculations


                                               16
New Zealand Applied Business Journal                                       Volume 1, Number 1, 2002


So                      PV interest    =   PV payments – PV principal repaid
                  n                         n                     n
So               PV interest
                 r 1
                                       =    PV payments   PV principal repaid
                                           r 1                  r 1



                                           P  n  R  Pi 1  i 
                                                                      1
                                       =

For our example                        =   120000 - 32165.43077

                                       =   $87 834.57




                                            17
Volume 1, Number 1, 2002                                           New Zealand Applied Business Journal




APPENDIX 8: ALGEBRAIC VERIFICATION OF THE REDUCING MORTGAGE
FORMULAE

                                                                               P
                                                                                 1  i 
                                                                                          r
To show that the present value of the principal repaid at step r is
                                                                               n

                                                                      P
The principal repaid at each step is the same and is equal to           . This is simply discounted to
                                                                      n
produce the necessary result

                                              P
                                                1  i 
                                                         r
PV of principal repaid                =
                                              n
                                               n
                                                     P
                                               n 1  i 
                                                              r
NPV of the n principal repayments     =
                                              r 1


                                              P n
                                                 1  i 
                                                           r
                                      =
                                              n r 1

                                              P
                                      =         a                    By definition
                                              n ni

The net present value of the repayments (including principal repayment and interest incurred)
is equal to P. Whereas in the case of the table mortgage the result follows directly from the
definition it might not be so apparent in this case.

At step r                Repayment    =       Principal repaid + Interest at step r

Principal outstanding at the beginning of step r

                                                              P
                                      =       P   r  1
                                                              n

                                                           P
Interest incurred at step r           =        P   r  1   i
                                                           n

                                              Pi
                                      =          1  n  r 
                                              n

                                              P               P
Repayment                             =          P   r  1   i
                                              n               n

                                              P                  P 
                                               n   P   r  1 n   i   1  i 
                                                                                        r
PV of the repayment                   =
                                                                    




                                               18
New Zealand Applied Business Journal                                                                            Volume 1, Number 1, 2002



                                                                         P Pi               
                                                                         n  n 1  n  r    1  i 
                                                                                                          r
                                                       =
                                                                                            

                                                                   P            Pi
                                                                     1  i   1  n  r 1  i 
                                                                             r                      r
                                                       =
                                                                   n            n

                                                       =          PV principal repaid + PV interest incurred
                                                                    n
So NPV                                                 =            PV principal repaid + PV interest incurred
                                                                   r=1


                                                       =
            n                                 n

          PV principal repaid +  PV interest incurred
          r=1                                 r=1


                                                                    n                  n
                                                                        P                 Pi
                                                                   n     1  i    1  n  r 1  i 
                                                                                  r                        r
                                                       =
                                                                   r 1              r 1 n


                                                                   P
The first sum has already been evaluated as                          a so it only remains to simplify the NPV of
                                                                   n ni
                           n
                                Pi                                   Pi n
the interest incurred.            1  n  r 1  i                   1  n  r 1  i 
                                                        r                                     r
                                                                   =
                        r 1    n                                     n r 1

This is an example of an arithmetico-geometric progression. Here is one way this sum can be
evaluated.
                  n

                 1  n  r 1  i 
                                              r
Let S =
                 r 1



                1  n 11  i             1  n  2 1  i   1  n  31  i  
                                        1                                  2                                 3
         =

                                                             1  n   n  1  1  i                 1  n  n 1  i 
                                                                                                 n 1                            n




                n 1  i    n  11  i    n  21  i                                   2 1  i              11  i 
                           1                          2                            3                           n 1                 n
         =                                                                                                                                      (1)

Multiply by 1  i 

1  i  S =    n 1  i    n  11  i    n  21  i                                 2 1  i              11  i 
                           0                           1                           2                           n2                   n 1
                                                                                                                                                (2)

(2) – (1)

                n  1  i   1  i                       1  i             1  i              1  i  
                                 1               2                        n2                 n 1             n
iS       =
                                                                                                                     

         =      n  an i



                                                                    19
Volume 1, Number 1, 2002                                          New Zealand Applied Business Journal


                                               P
So from above NPV interest incurred =             iS
                                               n

                                        =
                                               P
                                               n
                                                   
                                                 n  an i     
                                                       P
                                        =      P        a
                                                       n ni

By proving this independently it can be seen that the results are interconnected.

                           P           P
So NPV repayments =          an i  P  an i   =         P
                           n           n

It can be seen that these proofs can be verified immediately by referring to the spreadsheet.




                                               20

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:9
posted:5/19/2012
language:
pages:20
fanzhongqing fanzhongqing http://
About