3|EXCEL – Finance Functions 2

Challenges
,
1

BYU Student Author: @Dallin_Gardner
Reviewers: @Benjamin_Lau, @Carter_Lee
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

  • Excel

Need a program? Click here.

This is the second part of the Finance Function Challenge. Click here to participate in the challenge Finance Function Part 1.

Overview
SmartHome Co. sells smart home appliances including doorbells, CCTVs, smart light bulbs, home security products, etc. SmartHome has an international salesforce and generates millions of sales each year. You are told that the Southern California region is needing to purchase 10 new vans. You will use finance functions in Excel in order to make sure that the region is staying within the given budget of $8,300 a month for financing each year.

Instructions

  1. Download the data file, and start on the “Research (Rate and PV)” sheet
  2. Calculate what the max monthly payment can be per month in Cell B2, and then what the max monthly payment for each individual van is.
  3. After that assuming a $30,000 price find out what the highest APR (use rate function) depending on yearly plans.
  4. Now we want to finish getting an idea of what the terms should be. Use the PV function to calculate the max price that SmartHome can afford depending the rates and length of terms.
  5. Move onto “Plans Negotiated (PMT)” sheet
  6. Solve the highlighted cells, by finding the monthly payment for an individual van and then calculating the total monthly payment for all the 10 vans. Do this for both the Big and Small dealership
  7. Moving onto the “Expected Value (FV)” Sheet. Solve the weighted average matrix. Multiply Likelihood by the rate for each market, and then add the rates together. This gives us the expected depreciation or appreciation of the car for each year for the next 5 years.
  8. Use the future value function to find the expected Market Value of the cars for each of the next 5 years

Data Files

Suggestions and Hints

-Divide the rate by 12 to get monthly interest rate

-Multiply the years by 12 to get total months

-The future value of the loan will be 0

Ex. Look like this =pv(rate/12,years*12,payment,future value as 0)

Solution

Challenge3_Solution.xlsx
Solution Video: Challenge 3|EXCEL –Finance Functions 2