Section 14 Business and Financial Table of Contents
Section 14 ....................................................................................................................................................... 1 Business and Financial ................................................................................................................................... 1 Table of Contents ........................................................................................................................................... 1 Table of Figures.............................................................................................................................................. 2 Introduction ................................................................................................................................................ 3 Loan Amortization...................................................................................................................................... 4 Future Value ............................................................................................................................................... 7 Payment ...................................................................................................................................................... 8 Period Payment ........................................................................................................................................... 9 Interest Payment ........................................................................................................................................10 Interest Rate ...............................................................................................................................................11 Number of Periods .....................................................................................................................................12 Present Value of an Investment .................................................................................................................13 Double Declining Balance .........................................................................................................................14 Straight Line Depreciation.........................................................................................................................15 Accelerated Depreciation ..........................................................................................................................16 Internal Rate of Return ..............................................................................................................................17 Net Present Value ......................................................................................................................................18
1
Table of Figures
Figure 1 Loan Amortization Dialog ............................................................................................................. 4 Figure 2 Future Value Dialog ........................................................................................................................ 7 Figure 3 Payment Dialog ............................................................................................................................... 8 Figure 4 Period Payment Dialog ................................................................................................................... 9 Figure 5 Interest Payment Dialog ................................................................................................................10 Figure 6 Interest Rate Dialog.......................................................................................................................11 Figure 7 Number of Periods Dialog ............................................................................................................12 Figure 8 Present Value Dialog.....................................................................................................................13 Figure 9 Double Declining Value Dialog ....................................................................................................14 Figure 10 Straight Line Depreciation Dialog ..............................................................................................15 Figure 11 Accelerated Depreciation Dialog ................................................................................................16 Figure 12 Internal Rate of Return Dialog ....................................................................................................17 Figure 13 Net Present Value Dialog ............................................................................................................18
2
Introduction Personal and business financial planning typically attempts to increase the value or worth of the planner. In many societies one must borrow money to purchase major items such as homes and automobiles. In addition, many may discover they have some cash they can invest. A small entrepreneur may have borrowed funds for capital investment in his or her company and has a need to determine how to price a product or service to maintain a positive cash flow. While these activities are not specifically "statistics" in the sense of testing hypotheses or estimating parameters, they are often performed by the same individuals responsible for completing statistical analyses (for example, financial officers.) OpenStat contains a variety of procedures useful for financial planning. The procedures available are listed below.
Financial: Loan Amortization Financial: Sum of Years Digits Depreciation Financial: Straight Line Depreciation Financial: Internal Rate of Return Financial: Present Value Financial: Period Payment Financial: Payment Financial: No. of Periods Financial: Net Present Value Financial: Interest Rate Financial: Interest Payment Financial: Future Value Financial: Double Declining Value
3
Loan Amortization Assume you wish to buy a car for $20,000.00 over 5 years with 12 equal payments per year. You would like an amortization table that shows you how much interest you are paying each month (and total.) In addition, you would like to know at each month how much you still owe and how much you have paid on the amount borrowed. You select the Loan Amortization option and complete the dialogue box shown below:
Figure 1 Loan Amortization Dialog
There are several ways to enter data to obtain a loan amortization schedule. You can “drag” the squares within each scroll bar to a new position. As you do, the Amount, Interest Rate, Years or Number of Payments change in the respective text boxes. Alternatively, you can directly enter the values in each of the text boxes, pressing the enter key on your keyboard after each entry. The Payment box is shown immediately as
4
any of the values are changed. When you click the Print Amortization Schedule button, the following results obtained are:
Payment Schedule Program by W. G. Miller ---------------------------------------------------------------------------Name of Borrower : I.M. Costly Amount borrowed = $ 20000.00 at 5.00 percent over 5.0 years. ---------------------------------------------------------------------------PAYMENT PAYMENT INTEREST BALANCE TOTAL TOTAL NUMBER AMOUNT PAYED REMAINING INTEREST PAID ---------------------------------------------------------------------------9/13/2004 377.42 83.33 19705.91 83.33 377.42 9/13/2004 377.42 82.11 19410.59 165.44 754.85 9/13/2004 377.42 80.88 19114.04 246.32 1132.27 9/13/2004 377.42 79.64 18816.26 325.96 1509.70 9/13/2004 377.42 78.40 18517.24 404.36 1887.12 9/13/2004 377.42 77.16 18216.97 481.52 2264.55 9/13/2004 377.42 75.90 17915.45 557.42 2641.97 9/13/2004 377.42 74.65 17612.67 632.07 3019.40 9/13/2004 377.42 73.39 17308.63 705.45 3396.82 9/13/2004 377.42 72.12 17003.33 777.57 3774.25 9/13/2004 377.42 70.85 16696.75 848.42 4151.67 9/13/2004 377.42 69.57 16388.89 917.99 4529.10 ------------------------------------------------------------------------------------------------------------------------------------------------------PAYMENT PAYMENT INTEREST BALANCE TOTAL TOTAL NUMBER AMOUNT PAYED REMAINING INTEREST PAID ---------------------------------------------------------------------------9/13/2005 377.42 68.29 16079.76 986.28 4906.52 9/13/2005 377.42 67.00 15769.33 1053.28 5283.95 9/13/2005 377.42 65.71 15457.61 1118.98 5661.37 9/13/2005 377.42 64.41 15144.59 1183.39 6038.79 9/13/2005 377.42 63.10 14830.27 1246.49 6416.22 9/13/2005 377.42 61.79 14514.64 1308.28 6793.64 9/13/2005 377.42 60.48 14197.69 1368.76 7171.07 9/13/2005 377.42 59.16 13879.43 1427.92 7548.49 9/13/2005 377.42 57.83 13559.83 1485.75 7925.92 9/13/2005 377.42 56.50 13238.91 1542.25 8303.34 9/13/2005 377.42 55.16 12916.64 1597.41 8680.77 9/13/2005 377.42 53.82 12593.04 1651.23 9058.19 ------------------------------------------------------------------------------------------------------------------------------------------------------PAYMENT PAYMENT INTEREST BALANCE TOTAL TOTAL NUMBER AMOUNT PAYED REMAINING INTEREST PAID ---------------------------------------------------------------------------9/13/2006 377.42 52.47 12268.09 1703.70 9435.62 9/13/2006 377.42 51.12 11941.78 1754.82 9813.04 9/13/2006 377.42 49.76 11614.11 1804.58 10190.47 9/13/2006 377.42 48.39 11285.08 1852.97 10567.89 9/13/2006 377.42 47.02 10954.67 1899.99 10945.32 9/13/2006 377.42 45.64 10622.89 1945.63 11322.74 9/13/2006 377.42 44.26 10289.73 1989.90 11700.16 9/13/2006 377.42 42.87 9955.18 2032.77 12077.59 9/13/2006 377.42 41.48 9619.24 2074.25 12455.01 9/13/2006 377.42 40.08 9281.89 2114.33 12832.44 9/13/2006 377.42 38.67 8943.14 2153.00 13209.86 9/13/2006 377.42 37.26 8602.98 2190.27 13587.29 ------------------------------------------------------------------------------------------------------------------------------------------------------PAYMENT PAYMENT INTEREST BALANCE TOTAL TOTAL NUMBER AMOUNT PAYED REMAINING INTEREST PAID ---------------------------------------------------------------------------9/13/2007 377.42 35.85 8261.40 2226.11 13964.71 9/13/2007 377.42 34.42 7918.40 2260.54 14342.14
5
9/13/2007 9/13/2007 9/13/2007 9/13/2007 9/13/2007 9/13/2007 9/13/2007 9/13/2007 9/13/2007 9/13/2007
377.42 377.42 377.42 377.42 377.42 377.42 377.42 377.42 377.42 377.42
32.99 31.56 30.12 28.67 27.22 25.76 24.29 22.82 21.34 19.86
7573.97 7228.10 6880.79 6532.04 6181.83 5830.16 5477.03 5122.43 4766.35 4408.78
2293.53 2325.09 2355.20 2383.87 2411.09 2436.85 2461.14 2483.96 2505.31 2525.17
14719.56 15096.99 15474.41 15851.84 16229.26 16606.69 16984.11 17361.53 17738.96 18116.38
------------------------------------------------------------------------------------------------------------------------------------------------------PAYMENT PAYMENT INTEREST BALANCE TOTAL TOTAL NUMBER AMOUNT PAYED REMAINING INTEREST PAID ---------------------------------------------------------------------------9/13/2008 377.42 18.37 4049.73 2543.54 18493.81 9/13/2008 377.42 16.87 3689.18 2560.41 18871.23 9/13/2008 377.42 15.37 3327.12 2575.78 19248.66 9/13/2008 377.42 13.86 2963.56 2589.64 19626.08 9/13/2008 377.42 12.35 2598.48 2601.99 20003.51 9/13/2008 377.42 10.83 2231.89 2612.82 20380.93 9/13/2008 377.42 9.30 1863.76 2622.12 20758.36 9/13/2008 377.42 7.77 1494.10 2629.88 21135.78 9/13/2008 377.42 6.23 1122.90 2636.11 21513.21 9/13/2008 377.42 4.68 750.16 2640.79 21890.63 9/13/2008 377.42 3.13 375.86 2643.91 22268.06 9/13/2008 377.42 1.57 -0.00 2645.48 22645.48 ----------------------------------------------------------------------------
6
Future Value FutureValue returns the future value of an investment of PresentValue where Payment is invested for NPeriods at the rate of Rate per period. The PaymentTime parameter indicates whether the investment is an ordinary annuity or an annuity due (enter EndOfPeriod if payments are at the end of each period, StartOfPeriod if they are at the beginning). EXAMPLE: What is the value 20 years from now of a savings account if you invest $100.00 per month in an account that pays 5.25% annual interest, assuming that the interest is compounded monthly? Rate Per Period= 0.0525 / 12 = 0.004375 Number of Periods = 20 years times 12 months = 240 periods Payment = -100.00 (NOTE: payments are always negative) Present Value = 0.0 ANSWER: Future Value = 42311.1776128932 or approximately $42,311.18 Shown below is the dialog box you would complete for the above example:
Figure 2 Future Value Dialog
7
Payment Payment calculates the fully amortized payment of borrowing PresentValue at Rate percent per period over NPeriods. It assumes that interest is paid at the end of each period. FutureValue is the value that the investment will reach at some point. PaymentTime indicates whether the cash flows occur at the beginning or end of the period. Example: How much would you have to pay into a savings account monthly in order for that savings account to be worth $50,000.00 after 20 years, assuming that the savings account pays 5.25% annual percentage rate (APR) compounded monthly? Rate = 0.0525 / 12 = 0.004375 Number of Periods = 20 * 12 = 240 Present Value = 0.0 Future Value = 50000.00 Time of Payment = After the period Answer: Payment = -118.172083172565 NOTE: Payments are always indicated as negative Shown below is the dialogue box you would complete for the above example:
Figure 3 Payment Dialog
8
Period Payment Period Payment gives the part of the payment that is principal. The Interest Payment function gives the part of the payment that is interest. The Rate parameter is the interest rate. Period is the number of periods into the loan for which the principal is desired, and NPeriods is the number of periods of the loan. Future Value is the value the investment will reach at some point. Payment Time indicates whether the cash flows occur at the beginning or end of the period. EXAMPLE: What is the principle payment on payment 1 of an auto loan of $20,000.00 borrowed at 7.5% Annual Percentage Rate (APR) over four years with monthly payments? Rate = 0.075 / 12 = 0.00625 Present Value = 20000.00 Period = 1 Number of periods = 4 * 12 = 48 Future Value = 0.0 ANSWER: (Note: payments are always negative values) The first payment on the principle is about $72.02
Figure 4 Period Payment Dialog
9
Interest Payment Interest Payment calculates the portion of a loan payment that reflects the interest. Rate represents the fixed periodic interest rate. Period identifies the payment period. NPeriods is the number of periods of the loan. PresentValue represents the amount borrowed (the principal). FutureValue is the future value of the investment. PaymentTime indicates whether the cash flows occur at the beginning or end of the period. EXAMPLE: What is the interest payment on payment 1 of an auto loan of $20,000.00 borrowed at 7.5% Annual Percentage Rate (APR) over four years with monthly payments? Rate per period = 0.075 / 12 = 0.00625. Present Value = 20000.00. Period = 1, Number of periods = 4 * 12 = 48. Future Value = 0.0. ANSWER: -125.00 (Note: payments are always negative values) The first payment contains $125.00 of interest charges. The dialogue box for the calculations is shown below:
Figure 5 Interest Payment Dialog
10
Interest Rate InterestRate calculates the interest rate required in order for an investment of PresentValue, with periodic payments of Payment, to be worth FutureValue within NPeriods compounding periods. If NPeriods represents years, an annual interest rate results; if NPeriods represents months, a monthly interest rate results, and so on. The PaymentTime parameter indicates whether the cash flows occur at the beginning or end of the period. EXAMPLE: What is the Annual Percentage Rate (APR) on a four-year auto loan for a $20,000.00 car when the monthly payment is $483.58? Number of periods = 4 years * 12 months = 48. Payment = -483.58 (NOTE: payments are always negative.) Present Value = 20000, Future Value = 0.0, Payment Time = End of Period. ANSWER: Rate = 0.0625 * 12 or about 7.5% APR. The dialogue box for this procedure is shown below with the example entered:
Figure 6 Interest Rate Dialog
11
Number of Periods NumberOfPeriods computes the number of payment periods required for an investment of PresentValue to reach a value of FutureValue, while making regular payments of Payment and accruing interest at the rate of Rate per compounding period. PaymentTime indicates whether the cash flows occur at the beginning or end of the period. EXAMPLE: How long would you have to make payments on a car loan for a $20,000.00 car assuming that the payment is to be no more than $450.00, and that the annual percentage rate (APR) on the loan is 7.5% ? Rate = 0.075 / 12 = 0.00625 Payment = -$450.00 (NOTE- payments are always negative) Present Value = $20000.00 Future Value = 0.0 ANSWER: Number of Payments = 52.2301263064951 or about $53 per period.
Figure 7 Number of Periods Dialog
12
Present Value of an Investment PresentValue calculates the present value of an investment where Payment is received for NPeriods and is discounted at the rate of Rate per period. FutureValue is the value the investment may reach at some point. PaymentTime indicates whether the cash flows occur at the beginning or end of the period. EXAMPLE: What was the amount borrowed in a 7.5% Annual Percentage Rate (APR) four-year auto loan when the payment is $500.00 per month? Rate per period = 0.075 / 12 = 0.00625 Number of periods = 4 years * 12 months per year = 48 Payment = -500.00 Future Value = 0.0 Payment Time = End of Period ANSWER: Present Value = 20679.1855679664 or about $20679.19 was borrowed. The dialogue box for this procedure with the example entered is shown below:
Figure 8 Present Value Dialog
13
Double Declining Balance Double Declining Balance determines accelerated depreciation values for an asset, given the initial cost, life expectancy, end value, and depreciation period. EXAMPLE: What is the depreciation value for a computer with a life expectancy of three years if it initially cost $2,000.00 with no expected value at the end of the three years? Initial Cost = 2000.00 Life Expectancy = 3 years End Value = 0.0 Depreciation Period = 3 years ANSWER: Approximately $148.15 The answer is obtained using the dialogue box shown below:
Figure 9 Double Declining Value Dialog
14
Straight Line Depreciation SLN Depreciation calculates the straight-line depreciation allowance for an asset over one period of its life. The function divides the Cost minus the Salvage by the number of years of useful Life of the asset. Cost is the amount initially paid for the asset. Salvage is the value of the asset at the end of its useful life. To compute accelerated depreciation (allowing higher depreciation values in the first years of the assets life), use the SYD Depreciation (accelerated) function. EXAMPLE: What is the depreciation value that one may claim for a computer purchased for $2000.00 and expected to have a useful life of three years with no residual value? ANSWER: Approximately $666.67 Shown below is the dialogue box and example:
Figure 10 Straight Line Depreciation Dialog
15
Accelerated Depreciation SYD Depreciation (for "sum-of-years-digits depreciation") calculates depreciation amounts for an asset using an accelerated depreciation method. This allows for higher depreciation in the earlier years of an asset's life. Cost is the initial cost of the asset. Salvage is the value of the asset at the end of its life expectancy. Life is the length of the asset's life expectancy. Period is the period for which to calculate the depreciation. EXAMPLE: What is the depreciation value for the first period that one may claim for a computer purchased for $2000.00 and expected to have a useful life of three years with no residual value? ANSWER: $1000.00 for the first year. If computed for year 2 the answer is approximately $666.67 and if done for year 3 yields $333.33. Below is the dialogue box for this procedure with the problem entered for year 1:
Figure 11 Accelerated Depreciation Dialog
16
Internal Rate of Return Internal Rate Of Return determines the internal rate of return on an investment. It references the main grid that contains cash flow information and uses the supplied internal rate of return estimate to calculate results. Before using this function, open a file containing expected cash flow amounts over a period of time. It is assumed that the amounts are received at regular intervals. Negative amounts are interpreted as cash outflows, and positive amounts as inflows. The first amount must be a negative number, to reflect the initial investment. The following amounts can all be the same for each time period, or they can be different (including a mixture of negatives, positives, or zeros). Specify the estimated rate of return as the Guess parameter. Pass the array of expected cash flow amounts as the CashFlows parameter.
Figure 12 Internal Rate of Return Dialog
17
Net Present Value NetPresentValue calculates the current value of an array of estimated cash flow values, discounted at the given interest rate of Rate. PaymentTime indicates whether the cash flows occur at the beginning or end of the period. NetPresentValue helps determine how much an investment is currently worth, based on expected earnings, although its accuracy depends on the accuracy of the cash flows in the array. Before using this function, open a file containing expected cash flow amounts over a period of time. It is assumed that the amounts are received at regular intervals. Negative amounts are interpreted as cash outflows, and positive amounts as inflows. The first amount must be a negative number, to reflect the initial investment. The following amounts can all be the same for each time period, or they can be different (including a mixture of negatives, positives, or zeros). Specify the estimated rate of return as the Guess parameter. Pass the array of expected cash flow amounts as the CashFlows parameter.
Figure 13 Net Present Value Dialog
18