References:
When a customer wants to buy a new car from a dealership, the dealership gives the customer two options: lease-own or loan-purchase.
The lease-own option let the customer drive the car off by paying a fixed monthly installment for a pre-negotiated number of years (N) as the lease period. The monthly installment is calculated as the car price divided by N*12. However, for the convenience of having the car with a relatively low cost at the beginning, the customer must agree to pay a discharge fee at the end of the lease period to actually own the car. The discharge fee is calculated as a pre-negotiated percentage of the monthly installment for each month the customer paying the installment. These monthly discharge fees are accumulated until being paid by the customer at the end of the lease period.
The loan-purchase option is quite straight forward and standard. The customer takes a loan with a pre-negotiated monthly interest rate from the dealership to buy the car. The borrowed money is exactly the same as the car price, and that amount is considered as the initial loan balance. Then the customer starts to pay back the money month by month with a pre-determined monthly payment. For the sake of this assignment, this pre-determined monthly payment is always the same as the monthly installment calculated in the lease-own option.
The customer always makes the monthly payment at the end of each month. And the dealership will calculate the interest accrued for the month by multiplying the loan balance with the monthly interest rate. The customer's monthly payment will pay off the interest accrued in that month first, then the rest of the payment will pay down the loan balance. So after each payment, the loan balance would be the old loan balance minus the difference between the monthly payment and the accrued interest.
You are asked to build two Excel models to compare, that at the end of three years, how much the customer must pay to actually own the car at that moment. You can safely assume that the lease period is always longer than three years.
In the lease-own model, the money the customer needs to pay to actually own the car is the accumulated discharge fee plus the rest of the car price that's not paid yet.
In the loan-purchase model, the money the customer needs to pay to actually own the car is the loan balance at the end of the three years.
In one worksheet named "Lease-Own", have a table showing the following columns for 36 months:
At the end, calculate the total discharge fee accumulated for the 36 months, and calculate the payment needed for the customer to actually own the car at that moment. Highlight the two cells containing these two values.
In this model, treat the car price, the number of lease months (must be greater than or equal to 36), the monthly installment and the percentage of installment for the discharge fee as the model parameters, so that if each of these numbers changes, your model display should change accordingly. Note that the monthly installment should be a derived data piece, although it should be placed in the parameter area.
In a second worksheet named "Loan-Purchase", have a table showing the following columns for 36 months:
And in the end, highlight the cell that contains the loan balance at the end of the 36 months, which is also the payment needed for the customer to actually own the car at that moment.
In this model, treat the car price/initial loan balance, the interest rate, and the monthly payment as model parameters. Note that you should link the car price/initial loan balance and monthly payment in this model with the car price and monthly installment respectively in the "Lease-Own" model using cell reference.
Save both worksheets with your work in a workbook named your Lastname_A3.xlsx.
Before submitting your assignment, you need to check at least the following things:
How to submit:
To submit your assignment, name your solution file your Lastname_A3.xlsx, then login to your VIU Learn account, find the CSCI 112 course page, click on the "Assessment" drop-down menu, click on the "Assignments" item, then click on the folder named "A3". Then you can click on the "Add a File" button to browse and upload your solution file Lastname_A3.xlsx.
If you have difficulties to make the VIU Learn submission work, please email me to let me know and attach your Excel solution file with your email.