Excel 2010: What-If Analysis

TL;DR

Learn how to use Excel's What-If Analysis feature to determine the interest rate needed for a desired car loan payment.

Transcript

Let's say that I'm shopping around for a car loan, to try to find one that I can afford. And I'm using Excel to help me find the answer because I know that it's great at calculating things automatically. All I know right now is that the total loan amount should be $20,000, and the term should be 60 months, and the highest monthly payment I can affo... Read More

Key Insights

• ⚾ Excel's What-If Analysis feature, specifically Goal Seek, is helpful for determining unknown values based on desired outcomes.
• ☠️ The PMT function in Excel calculates the payment for a loan based on constant payments and a constant interest rate.
• ☠️ Inputting the correct interest rate is crucial for accurate loan payment calculations in Excel.
• ❓ The narrator recommends practicing the What-If Analysis feature to become comfortable using it.
• 😫 When using Goal Seek, ensure the cell containing the desired value is set as the target and input the desired value with the appropriate sign.
• ☠️ Excel assumes a zero interest rate when the interest rate cell is blank.
• ☠️ Dividing the yearly interest rate by 12 is necessary to obtain the monthly interest rate in Excel calculations.

Questions & Answers

Q: How can Excel's What-If Analysis feature help with calculating car loan payments?

Excel's What-If Analysis feature, specifically Goal Seek, allows you to input a desired value (like a monthly payment) and determine the unknown value (like the interest rate) needed to achieve that desired value.

Q: Why is the PMT function not producing the correct payment in this scenario?

The PMT function assumes a zero interest rate since the interest rate cell is blank. Therefore, the payment calculated will not be accurate without inputting the correct interest rate.

Q: What does the narrator use the PMT function for?

The narrator uses the PMT function to calculate the payment for a loan based on constant payments and a constant interest rate.

Q: How does the narrator use Excel's Goal Seek in this scenario?

The narrator uses Goal Seek to find the interest rate needed to achieve a monthly payment of $400. By setting the cell containing the payment as the target and inputting the desired payment (-400 since it needs to be negative), Goal Seek determines the interest rate.

Summary & Key Takeaways

• The narrator wants to find a car loan with a monthly payment not exceeding $400, a loan amount of $20,000, and a loan term of 60 months.

• They use Excel's PMT function to calculate the payment for a loan but end up with an incorrect answer due to a blank interest rate.

• The narrator demonstrates how to use Excel's What-If Analysis feature, specifically Goal Seek, to find the interest rate needed to achieve a $400 monthly payment.