Document Sample

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.04550 = 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 Pr 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 = Pi .......... (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 1d .......... (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 1i 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 11 i 1 n 2 1 i 1 n 31 i 1 2 3 = 1 n n 1 1 i 1 n n 1 i n 1 n n 1 i n 11 i n 21 i 2 1 i 11 i 1 2 3 n 1 n = (1) Multiply by 1 i 1 i S = n 1 i n 11 i n 21 i 2 1 i 11 i 0 1 2 n2 n 1 (2) (2) – (1) n 1 i 1 i 1 i 1 i 1 i 1 2 n2 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 |

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.