Amortization Table for a 5 Year Car Loan

Document Sample
Amortization Table for a 5 Year Car Loan Powered By Docstoc
					                                                                            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 A mortizat ion ...................................................................................................................................................... 4
  Future Value ................................................................................................................................................................. 7
  Payment ......................................................................................................................................................................... 8
  Period Pay ment ............................................................................................................................................................ 9
  Interest Payment ........................................................................................................................................................ 10
  Interest Rate................................................................................................................................................................ 11
  Nu mber of Periods .................................................................................................................................................... 12
  Present Value of an Investment............................................................................................................................... 13
  Double Declin ing Balance ....................................................................................................................................... 14
  Straight Line Depreciation ....................................................................................................................................... 15
  Accelerated Depreciat ion ......................................................................................................................................... 16
  Internal Rate of Return ............................................................................................................................................. 17
  Net Present Value ...................................................................................................................................................... 18




                                                                                          1
                                                               Table of Figures
Figure   1 Loan A mortization Dialog ........................................................................................................................... 4
Figure   2 Future Value Dialog ....................................................................................................................................... 7
Figure   3 Pay ment Dialog ............................................................................................................................................... 8
Figure   4 Period Pay ment Dialog .................................................................................................................................. 9
Figure   5 Interest Pay ment Dialog ............................................................................................................................. 10
Figure   6 Interest Rate Dialog..................................................................................................................................... 11
Figure   7 Nu mber of Periods Dialog ......................................................................................................................... 12
Figure   8 Present Value Dialog .................................................................................................................................. 13
Figure   9 Double Declin ing Value Dialog................................................................................................................ 14
Figure   10 Straight Line Dep reciation 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 Amo rtization

Financia l: Su m o f Years Digits Depreciation

Financial: Straight Line Depreciat ion

Financial: Internal Rate of Return

Financial: Present Value

Financial: Period Pay ment

Financial: Pay ment

Financial: No. of Periods

Financial: Net Present Value

Financial: Interest Rate

Financial: Interest Payment

Financial: Future Value

Financial: Double Declin ing 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 Amortizati on Dial og


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      18 04.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       377.42       32.99       7573.97     2293.53     14719.56
  9/13/2007       377.42       31.56       7228.10     2325.09     15096.99
  9/13/2007       377.42       30.12       6880.79     2355.20     15474.41
  9/13/2007       377.42       28.67       6532.04     2383.87     15851.84
  9/13/2007       377.42       27.22       6181.83     2411.09     16229.26
  9/13/2007       377.42       25.76       5830.16     2436.85     16606.69
  9/13/2007       377.42       24.29       5477.03     2461.14     16984.11
  9/13/2007       377.42       22.82       5122.43     2483.96     17361.53
  9/13/2007       377.42       21.34       4766.35     2505.31     17738.96
  9/13/2007       377.42       19.86       4408.78     2525.17     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 Di alog




                                           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 Dial og




                                             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 Dial og




                                            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 Dial og




                                             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 Di alog




                                            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 Dial og




                                          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 Val ue Di alog




                                            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 Li ne Depreciati on Dial og




                                                 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 a nswer 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 Val ue Di alog




                                            18

				
DOCUMENT INFO
Description: Amortization Table for a 5 Year Car Loan document sample