- Calculating Loan Payments
- Calculating Principal Payments
- Working with Future Value
- Using the Present Value Function
- Calculating Interest Rate
This chapter is from the book
This chapter is from the book
This chapter is from the book
Calculating Interest Rate
In the calculations that have been presented so far in this chapter, the interest rate was a variable that you either know or had to estimate. But what if you know the other parameters of a loan or other transaction but not the interest rate? Then you can calculate it using the RATE function.
Here’s an example. Suppose you want to take out a $10,000 personal loan from a friend or family member. They agree with the condition that you repay the loan at $300 per month for three years. You’d like to determine the effective interest rate for this deal—here’s where the RATE function comes in. Is this a good deal, or would you be better off taking a loan from the bank?
The RATE function has the following syntax:
The first three arguments are required:
nper is the number of payments for the loan.
pmt is the payment amount.
pv is the present value—the amount of the loan.
The other three arguments are optional:
fv is the future value of the loan, the balance when the payments are completed. Usually this will be 0, which is what Excel assumes if the argument is omitted.
type specifies when payments are made. Use 0 (the default if the argument is omitted) if the payments are made at the end of the period, 1 of they are made at the start of the period.
guess is your guess at the answer—your estimate of the interest rate. Because of the way RATE performs its calculations using a trial-and-error iteration, it requires a guess and then works from there to calculate the actual value. If you omit this argument, the value 10% (annual) is used.
As with all Excel financial functions, the period for the rate must match the other arguments. For example, if you enter arguments that include monthly payments, the RATE function’s result will be the monthly interest rate, and you must multiply this by 12 to get an annual rate.
Let’s try out the RATE function. Start with a blank worksheet and then follow these steps:
Enter the labels Principal, Monthly payments, Term in months, and Annual rate in cells B2 though B5, in order.
Format cells C2 and C3 as currency with two decimal places.
Format cell C5 as percentage with two decimal places.
Enter the following formula in cell C5: =12*RATE(C4,C3,C2).
Figure 3.6 shows this worksheet using the sample data from above. You can see that the effective rate on this loan is just a hair over 5%, which is quite reasonable.
)
Figure 3.6 Using the RATE function to calculate the interest rate on a loan.
This chapter has shown you how you can use Excel’s built-in functions to perform commonly needed financial calculations. With this information under your belt, you are ready for the next chapter that covers net worth and presents the first of the book’s Excel templates.