Directions:

In this assignment, we are going to look at the piano incorporated company. They are looking at buying a truck to move pianos. There are 2 ways to look at their cost for the truck. We are going to look at the cost based on different interest rates (one variable data table) and then we will look at how much can be afforded with a fixed interest rate and $500 (goal seek). This assignment has 2 parts.

You will create:

• One variable data table worksheet (15 pts)
• Goal seek worksheet(15 pts)

Download the data file and complete the following:

Data Table:

• Create a PMT function in cell B7. Make sure to use the current interest rate and time period given.
  • B7: = PMT(B3*(B3/12),B5,-B2)
  • B8: =B7*B5
  • B9: =B8-B2
  • C12: =B7
  • C13:=B8
  • C14:=B9
• Create a one-variable data table

Solution

Make sure that you format the cells to match what is given.

Goal Seek

In this example, we are going to see how much we can afford with $500 a month. Complete the following:

• Create a PMT function in cell B9. Make sure to use the current interest rate and time period given.
  • B7: =PMT(B7/12,B8*12,-B6)
• Perform the goal seek analysis. You should get $29,176 as a value in cell B4.
• Please note that cell B7 is 5.5% rounded to 6%.

Solution