- Calculating Loan Payments
- Calculating Principal Payments
- Working with Future Value
- Using the Present Value Function
- Calculating Interest Rate

## 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**, and**Term in months**in cells B2 though B5, in order.**Annual rate**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.