Please help me with the formulars and the samples as to how the answers should l
ID: 2789603 • Letter: P
Question
Please help me with the formulars and the samples as to how the answers should like are being provided here. I need the formulars clearly done on an excel file with the instructions.
of return for the investment. 14-10. You are considering purchasing a new car. The price would be $18,239. You would pay $2,000 now and the rest monthly in a four-year loan. The automobile dealership is offering a sales promotion where either (a) you will receive a $1,000 rebate check right now and the annual interest rate on the loan will be 11.9% or (b) the annual interest rate on the loan will be 1.9% but there is no rebate. Create a worksheet to compare the two options by calculating the present value of each of the options, assuming an 8% discount rate. Which is the better deal? Write a paragraph to justify your answer.Explanation / Answer
The Present Value will be the future payments that you will make discounted by the discount rate. This is the same as saying how much money you need to put into the bank at present at an interest rate that equals the discount rate (8%) in order to have your target amount in 4 years.
Scenario 1
Calculate Target Amount
Pay 2000 Down Payment
Loan amount = 18,239 - 2000 = 16,239
At a compounded annual interest of 11.9%
16,239 X 1.119 = 18,171 (first year)
18,171 X 1.119 = 20,334 (second year)
20,334 X 1.119 = 22,754 (third year)
22,754 X 1.119 = 25,461 (fourth year)
But its better to do it this way
16,239 X (1.119)^4
= 16,239 X 1.5677 = 25,457 (the difference is due to rounding off errors)
Therefore, at a straight compounded interest of 11.9% per annum without any allowance for the money you are paying monthly you will be paying 25,457 in lieu of the 16,239 that you owe. This is the target (future) amount.
If you are paying this amount over four years, you will be paying
25,457/4 = 6,364.25 per year
Now for the discount rate calculation (present value).
How much money will you need to put into a bank at 8% interest in order to have 25,457 in 4 years' time? (You need to "devalue" the money by 8% per year compounded). This is the same as calculating interest but in reverse.
Year 1 - Pay 6,364.25 at Discount Rate = 1/(1+ 0.8)^1 = 6,364.25 X 0.9259 = 5,892.66
Year 2 - Pay 6,364.25 at Discount Rate = 1/(1+ 0.8)^2 = 6,364.25 X 0.8573 = 5,456.07
Year 3 - Pay 6,364.25 at Discount Rate = 1/(1+ 0.8)^3 = 6,364.25 X 0.7938 = 5051.94
Year 4 - Pay 6,364.25 at Discount Rate = 1/(1+ 0.8)^4 = 6,364.25 X 0.7350 = 4,677.72
Therefore the money that you will be paying over 4 years, if reduced to its value at present will be
5,892.66 + 5,456.07 + 5,051.94 + 4,677.72 = 23,078.39
Add the 2,000 down payment = 25,078.39
And subtract the cash rebate of 1,000 = 24,078.39
Therefore the Present Value for the 18,239 car that you are going to buy is actually 24,078.39 for Scenario 1.
The same sort of calculation has to be done for Scenario 2 but you don't have to account for any cash rebate in the final calculation.
Therefore the Present Value for the 18239 car that you are going buy is actually 25078.39 for Scenario 2.