# 3|EXCEL – Finance Functions 2

**URL:** <https://www.techhub.training/t/3-excel-finance-functions-2/4806>\
**Category:** Challenges\
**Tags:** beginner, finance\
**Created:** [April 10, 2025, 3:40am UTC](https://www.techhub.training/t/3-excel-finance-functions-2/4806 "2025-04-10T03:40:15Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dallin\_Gardner](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/dallin_gardner/32/5553_2.png) [@Dallin\_Gardner](https://www.techhub.training/u/Dallin_Gardner)\
**Post date:** [April 10, 2025, 3:40am UTC](https://www.techhub.training/t/3-excel-finance-functions-2/4806/1 "2025-04-10T03:40:15Z")

</div>

_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](https://www.techhub.training/t/information-about-software-used-on-techhub-training/138).

This is the second part of the Finance Function Challenge. Click [here](https://www.techhub.training/t/227-excel-finance-function-npv-irr-and-pv/2715) 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**

- [Challenge3\_Excel\_Finance\_Functions\_2](https://byu.box.com/shared/static/vlan8oc9z8b1wqc9fxdwu3745z6zsogc)

> **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](https://byu.box.com/shared/static/gzwmubb5gkt6qfyscv5v8quotgq31op6)  
_Solution Video:_ [Challenge 3|EXCEL –Finance Functions 2](https://youtu.be/MtcHgzk81To)

[](https://youtu.be/MtcHgzk81To)