Think & Done

Excel RATE Calculator

type in the authorization code in the box located below:

Results RATE = 30%

Input Data Type in the number of periods (NPER)   Type in the initial investment (PV)   Type in the periodic contribution (PMT)   End of Period Start of Period Type in the future net worth (FV)

Instructions

As you recall Excel provides a RATE formula or function that calculates rate of return of various sorts. It accepts few arguments such as NPER or number of periods (this may be years, quarters, months, weeks, or days), the argument PV is the present value of the investment amount, PMT is the periodic payment, and FV is the total or net future worth of the investment. Let me explain using this online RATE calculator with sample data. Say you invested an initial amount of $1000 in a bank account with another deposit at the end of the year in amount of $5000. The future value or final value of this investment is $7,500. We are to find the rate at which an initial deposit of $1000 plus a periodic deposit of $5000 at the end of the year equal $7500. Here are the steps listed below:

1. In the first box type in 1 for NPER for the time period between deposits
2. Type in -5000 in the second box for PV this is the present value of the investment or amount of the deposit thus a negative amount
3. Type in -1000 in the third box for PMT this is the periodic payment at the end of the first year, it too is a negative amount as it is the outgoing cash
4. And in the fourth box we will type in 7500 this is the future value of our investment, we are trying to find the RATE at which this amount equals a compounded initial deposits plus the periodic deposits
5. Finally a click on CALCULATE button will help find RATE, this is a periodic rate thus if the deposit frequency of PMT were monthly we will need to annualize it

Calculating IRR with this RATE Calculator

If you have a series of constant cash flow amounts then this RATE Calculator will help you find IRR with minimal data entry. Let me walk you through an example IRR Calculation with this RATE calculator. Say you had invested $10,000 and you expect to gain $3,000 for each of the next 5 years. Here you would like to know internal rate of return or IRR. Follow the steps listed below to find IRR with this RATE calculator.

1. Type in 5 in the first box for NPER
2. Type in -10000 in the second box for PV as this is the outgoing amount
3. Type in 3000 in the third box for PMT as this the series of expected payments
4. Type in 0 in the last box for FV since the net worth of this investment is set to zero to find IRR
5. Finally a click on CALCULATE button will help find internal rate of return the IRR

Calculating YTM for non zero coupon bond with this RATE Calculator

You can also use this RATE calculator to find YTM or yield to maturity on bonds. We will illustrate calculating YTM for non zero coupon and zero coupon bonds. Let us consider Google Corp. issues a bond with maturity value of $1000 with a 10% semi annual coupon, the market price of the bond stands at $1100 with 15 years till maturity. You are required to find market rate of return or the YTM for this bond. Follow the steps outlined below to find YTM with this Excel RATE calculator

1. Type in 30 in the first box for NPER (we used 30 since the interest payments are twice a year thus 15 times 2)
2. Type in -1100 in the second box for PV as this is this is the present market value of the bond
3. Type in 50 in the third box for PMT as this the series of expected interest payments (Interest = Coupon Rate x Maturity Value / 2)
4. Type in 1000 in the last box for FV since the net worth of this investment at maturity is 1000
5. Finally a click on CALCULATE button will help find yield till maturity the YTM

Calculating YTM for zero coupon bond with this RATE Calculator

Say we had a zero coupon bond that has a maturity value of $1000 with ten years till maturity. And the current market price is $350. Your task is to find the YTM for this zero coupon bond

1. Type in 10 in the first box for NPER
2. Type in -350 in the second box for PV as this is this is the present market value of the bond
3. Type in 0 in the third box for PMT as this the series of expected interest payments (Interest = Coupon Rate x Maturity Value / 2)
4. Type in 1000 in the last box for FV since the net worth of this investment at maturity is 1000
5. Finally a click on CALCULATE button will help find yield till maturity the YTM