This is an input screen that allows you to enter variables describing the client’s auto buying habits and their perceptions on the value of cars. The Assumed Inputs: RETURN ON INVESTMENT: is the rate of return the client could earn on an investment if he had invested the money, as opposed to spending it to purchase a car. YEARS BETWEEN TRADES: is the frequency in years that the client trades or buys a new (different) car. For example, if the client trades in his car every four years, then his YEARS BETWEEN TRADES are 4. This is true, even if he pays off the car in three years. It is also true if he finances the cars for six years. The financing periods (months) are entered on screen two and are independent of YEARS BETWEEN TRADES. CAR BUYING YEARS LEFT: is the number of years that the client will purchase cars. For example, a 21-year-old person that plans to purchase cars until the age of 80 has 60 CAR BUYING YEARS LEFT. CAR INFLATION RATE: is the client’s perception of how car prices will increase over time. It may or may not be the same as the general inflation rate. SALES TAX ON CARS: is the percent sales taxes and fees imposed on car purchases. The program only applies sales taxes to the difference between the new car cost and the trade in value. CAR LIFE IN YEARS: is the client’s perception of how long (in years) a car lasts. Typical values are 4 to 10 years. This input and the input YEARS BETWEEN TRADES determine the trade in value of the previous car. The trade in value is (1 - (YEARS BETWEEN TRADES / CAR LIFE IN YEARS)) times NEW CAR COST. Thus entering a long CAR LIFE or entering short YEARS BETWEEN TRADES increase trade in value. Similarly, entering a short CAR LIFE or entering long YEARS BETWEEN TRADES decrease trade in value. Click CALCULATE to display a summary of the opportunity cost of JUST THE INTEREST of buying Cars over the client’s auto buying life. Click DOCUMENTATION to display any one of three spreadsheets used in the calculations of Trading Cars Loop. |
No example script necessary for this screen. |
This screen calculates the •number of Cars purchased over the client’s car buying life •the value of each car purchased adjusted for inflation, trade-in, and sales taxes •the monthly balances for car debt •future value (opportunity cost) of JUST the INTEREST part of the car payments. Compounding is monthly as opposed to annually. The calculations are performed in three worksheets. These worksheets may be displayed by clicking documentation and then selecting one of the three worksheets to display. Each worksheet is calculated as follows. ============================================================== WORKSHEET ONE: COST OF EACH CAR PURCHASED Column One displays the car number. Each car purchased is assigned a number. For example, the third car purchased is car number 3. Column Two displays the years (from the initial car purchase) during which the car is purchased. Notice the first car is always purchased at the start of year 1. This mean a car purchased four years later would be purchased at the beginning of year 5. Column Three displays the month (from the initial car purchase) during which the car is purchased. For example, a car purchased four years after the initial purchase would be purchased at the beginning of month 49 (beginning of year 5). Column Four displays the new car cost adjusted for CAR INFLATION. The equation used is: inflation adjusted cost = initial car cost x (1 + CAR INFLATION/100) ^ YEARS For example, if a car cost $20,000 and car inflation is 4%, then four years later, the car would cost: inflation adjusted cost = $20,000 x (1 + 4/100) ^ 4 = $23,397.17 Compounded is annual for this calculation. The caret ^ means raise to a power. Column Five is the trade in value for the previous car. The trade in value is calculated using the following equation: trade in value = (1 - (YEARS BETWEEN TRADES / CAR LIFE IN YEARS)) x NEW CAR COST For example, if the previous car is $20,000, YEARS BETWEEN TRADES is 4 and CAR LIFE IN YEARS is 6, then the trade in value = (1 - (4/6)) * $20,000 = $6,666.66 Column Six is the after trade cost. It simply subtracts the trade in value from the new car cost. For example, a new car cost of $23,397.17 and a trade in value of $6,666.66 gives an after trade in cost of $16,730.51. Column Seven is the sales tax. It is calculated only on the after trade in cost. For example, if the CAR SALES TAX rate is 10%, then the sales tax on the after trade value of $16,730.51 is . 1 x $16,730.51 = $1,673.05 Column Eight is the Adjusted Car Cost. It is the After Trade Cost + Sales Tax. Using the values from the above example, the adjusted cost is: $16,730.51 + $1,673.05 = $18,403.56 ============================================================== WORKSHEET TWO: MONTHLY BALANCES, PAYMENTS & OPPORTUNITY COSTS Column One is the months elapsed since the first car was purchased. The total number of months will be a function of the total number of Cars purchased and the months of car financing. Specifically, it is the Number of Cars x Years Between Trades x 12 + Number Of Months of Remaining Car Financing (if any). Column Two is the number of the car purchased. This column is zero unless a new car is purchased during the month. For example, with a YEARS BETWEEN TRADES OF four years, the second car (Car Number 2) would be purchased during the 49th month (or the beginning of the fifth year). Column Three is the Adjusted Car Cost. It is transferred from or looked up in table one. Column Four is the balance of Car Debt at the beginning of the month. It is equal to the balance at the end of the previous month plus any new car purchase (if any). Column Five is monthly payment. It is calculated using the PAYMENT function (which is available on a financial calculator or worksheet). It is re-calculated each time a new car is purchased based on the current outstanding balance, interest rate, and months to finance. For example, with the following inputs: •$22,000 beginning of month balance •8% annual interest rate •60 months financing. Enter -$22,000 as the present value in a financial calculator Enter an interest rate of 8% and set compounded to 12 times per year (monthly) Enter 60 period (months) Set beg/end to end of period (car loans are paid at end of period) Set future value to zero (we want to pay the car off in 60 months) Set payment to zero (we wish to compute this value) Press calculate to calculate a monthly payment of $446.08 Column Six is the interest part of the monthly payment. It is equal to the monthly car interest rate time the balance at the beginning of the month. For example, with an annual interest rate of 8% and a beginning of month balance of $22,000; the interest for the current month would be .08/12 x $22,000 = $146.66. Column Seven is the principal part of the monthly payment. It is equal to the monthly payment minus the interest for the current month. Continuing with the above example, the principal part of the payment would be 446.08 minus $146.66 which equals $299.42. Column Eight is the balance at the end of the month. It is equal to the balance at the begin of the month minus the principal part of the monthly payment. Continuing with the above example, the balance at the end of the month is $22,000 minus $299.42 which equals $21,700.58. Column Nine is the cumulative interest paid on cars. It accumulates the interest shown in column six. Column Ten is the opportunity cost of (just) the interest. It is calculated using the client’s RETURN ON INVESTMENT and his Car Buying Years. The opportunity cost is projected to the end of the client’s CAR BUYING YEARS using the following equation for each month: opportunity cost for current month =(monthly interest) x ( 1 + return on investment/12) ^ (car buying months left) - monthly interest Continuing with the above example and assuming a return on investment of 8% and 251 buying months left, the opportunity cost of the $146.66 in interest is: $146.66 x ( 1 + .08/12) ^ 251 - $146.66 = $630.70 where ^ means raise to a power. Column Eleven is the cumulative opportunity cost of interest. It accumulates the opportunity cost of interest shown in column ten. Column Twelve is the sum of cumulative interest and cumulative opportunity cost of interest. It is the sum of columns nine and eleven. It represents the money the client would have accumulated by investing the interest (paid on cars each month) at his or her investment rate of return. ============================================================== WORKSHEET THREE: OPPORTUNITY COST OF INTEREST FOR EACH CAR Column One is the car number. This is the same car number described in column two of table two. Column Two is the (cumulative opportunity cost of interest plus the cumulative interest cost) at the time each car is purchased. This value is transferred from column 12 in table two. Column three is the (cumulative opportunity cost of interest plus the cumulative interest cost) attributed to each car. It is simply the cumulative costs for each car minus the cumulative costs for the previous car. Stated differently, column three (for a given car number) is column two (for the same car number) minus column two (for the previous car number). In the case of car number one, the previous value of column two is zero. |