Calculate interest rate for loan

Calculate interest rate for loan

To calculate the periodic interest rate for a loan based on the loan amount, payment periods, and payment amount, use the RATE function.

How to calculate the periodic interest rate?

  1. Select cell C7.
  2. Type =RATE(C4, -C3, C2)*C5
  3. Here, C4 is the number of periods, C3 is the monthly payment, C2 is the loan amount, C5 is the loan term.
  4. Press Enter.
  5. The formula returns the interest rate over the loan term.

Example

In this example, we have a dataset in range B2:C7 detailing loan parameters, including loan amount, monthly payment, periods (in months), and compounding periods per year. The goal is to calculate the annual interest rate based on these values.

Formula:

=RATE(C4, -C3, C2)*C5

Calculate interest rate for loan

The formula in cell C7 uses the RATE function to compute the monthly interest rate and multiplies it by the number of compounding periods per year to derive the annual rate.

Explanation

Let us see how the formula works:

The RATE function calculates the interest rate for an investment or loan based on periodic, constant payments and a constant interest rate. RATE uses the following syntax:

RATE(nper, pmt, pv, [fv], [type], [guess])

  • nper (C4): This represents the total number of payment periods. In this case, it’s 48 months.
  • pmt (-C3): The payment made each period. Payments are considered outflows (negative values), so -C3 indicates a payment of -$235 per month.
  • pv (C2): This is the present value, or the total loan amount. Here, it’s $10,000.
  • [fv]: Future value. It defaults to 0 if omitted, which is common for loan calculations since the goal is to pay the balance down to 0.
  • [type]: Indicates when payments are made: 0 (default): Payments are made at the end of the period. 1: Payments are made at the beginning of the period.
  • [guess]: An initial estimate for the rate. This is optional and defaults to 10% if omitted.

Excel solves this internally using an iterative approach to find the rate that satisfies the equation where
r is the monthly interest rate, n is the number of periods, and PMT is the payment.

formula

Finally, multiply the result by 12 (C5) to annualize it.

You can use PMT to verify payments:

=PMT(rate, nper, pv)

The formula allows recalculating the monthly payment if the interest rate and periods are known.