Suppose that you decide to lease a car. At the end of the 48-month lease period, you need to make a lump-sum payment of $5000 if you want to keep the car. You decide to save up, just in case you decide to keep the car; if you don’t keep this car, you will still have saved a good down payment on a new car. You feel comfortable with saving $70/month (over and above your lease payments). How high an annual nominal interest rate on savings do you need to accumulate $5000 in 48 months, with interest compounded monthly?
How would I do this in Excel 2007? Is there a specific function I can use?
To calculate the annual nominal interest rate needed to accumulate $5000 in 48 months, with interest compounded monthly, you can use the Excel function called RATE.
Here's how you can use the RATE function in Excel 2007:
1. Open a new Excel spreadsheet.
2. In cell A1, enter the value $5000. This represents the future value you want to accumulate.
3. In cell A2, enter the value -70. This represents the monthly savings amount, entered as a negative value since it represents an outflow of cash.
4. In cell A3, enter the value 48. This represents the number of months.
5. In cell A4, enter the formula "=RATE(A3,A2,A1)". This formula uses the RATE function to calculate the interest rate needed.
6. Press Enter to get the result. The value in cell A4 will be the annual nominal interest rate needed to accumulate $5000 in 48 months, with interest compounded monthly.
Note: The RATE function in Excel calculates the interest rate per period, so you need to specify the number of periods (months in this case) and the cash flows (savings and future value) as negative or positive values as appropriate.
Keep in mind that the result from the RATE function will be a decimal value. To convert it to a percentage, you can format the cell as a percentage by selecting the cell and choosing the Percentage formatting option in the cell format settings.