Question

Show work (excel if possible but show formulas you used in excel):

If I buy a new car and pay $399 a month for 5 years and the maintenance costs are $50 the first year and increase by $200 a year for each year after that, how much money would I have to barrow today at a nominal interest rate of 9% to cover my monthly payment and my yearly maintenance bill?

Answer 1

Amount to be borrowed today = Present value of monthly payments + Present value of yearly maintenance costs

Finding present value of monthly payments discounted at monthly rate

Monthly payment = $399, No of payments or months = 12 x No of years = 12 x 5 = 60, Nominal interest rate = 9%

Monthly rate = Nominal interest rate / 12 = 9% / 12 = 0.75%

When all the payments are equal, we can find the present of monthly payments using PV function in excel.

Formula to be used in excel: =PV(rate,nper,-pmt)

Using PV function in excel, we get present value of monthly payment = 19221.1760

Calculating present value of yearly maintenance costs discounted at nominal interest rate

Nominal interest rate = 9%

Maintenance cost of year 1 = $50, As we know maintenance costs increase by $200 we get following table

Year	1	2	3	4	5
Maintenance Cost	50	250	450	650	850

Since maintenance cost are non-constant, we will find the present value of maintenance costs using NPV function in excel

Formula to be used in excel: =NPV(rate,values)

where rate = nominal interest rate and values are maintenance costs for year 1 to 5

Using NPV function in excel, we get present value of maintenance costs = 1616.6921

Now, Amount to be borrowed today = Present value of monthly payments + Present value of yearly maintenance costs

Amount to be borrowed today = 19221.1760 + 1616.6921 = 20837.8681 = 20837.87 (rounded to two decimal places)

Hence, amount be borrowed today to cover monthly payment and yearly maintenance costs = 20837.87