Calculating Interest Rate in TVM Problems
Interest rate is a percentage measure of interest, the cost of money, which accumulates to the lender.
The interest is either paid through periodic payments, for example in case of bonds, or accumulated over the period of loan/investment such that it is paid at the maturity date together with principal amount of loan/investment, for example in case of certificates of deposit, etc. Other investment structures such as annuities are also based on interest. They either represent (a) a single value today i.e. a present value that grows at an interest rate while allowing equal cash flows after equal interval or (b) a stream of equal cash flows that grow to a certain value to a single value in future i.e. the future value.
There are two types of interest: (a) simple interest and (b) compound interest. Simple interest is where interest for any period is calculated based on the principal balance only and compound interest is where the interest is charged on the principal balance plus interest accumulated to the date of calculation.
Simple Interest Rate
Given a present value and a future value based on simple interest, interest rate can be found out by solving the following equation for r:
Future Value = Present Value × (1 + r × Time)
|r =||Future Value||×||1|
Simple interest rate can also be calculated using Excel INTRATE function.
Compound Interest Rate
Given a present value, a series of equal values that occur after equal intervals in future and/or a single value at some future date that are subject to compound interest, the interest rate can be worked out using either of the following equations:
|PV = PMT ×||1 − (1 + RATE)-NPER||+||FV|
|RATE||(1 + RATE)NPER|
|FV = PV × (1 + RATE) + PMT ×||(1 + RATE)NPER − 1|
PV is the present value i.e. a single sum at t=0,
FV is the future value i.e. a single sum at t=NPER,
PMT is the periodic equal cash flow that occurs after equal interval,
NPER is the total number of periods between PV and FV, and
RATE is the periodic compound interest rate.
The above equations look over-whelming even though they are just different forms of one relationship . We can only solve them using the hit-and-trial method. We plug different values and keep notching it up and down till we get a value that best fits the equations. Alternatively, we can use Excel RATE function.
Your company has obtained a $20 million worth of equipment on a 5-year lease. You are required to pay $1 million quarterly and $5 million at the end of the lease term. You need to calculate the interest rate implicit in the lease.
We have a value at t=0, the present value of $20 million, a future value after 5 years of $5 million and 20 (=5 years multiplied by 4 payments per year) quarterly payments of $1 million constituting an annuity. Further, we know that leases are subject to compound interest. We need to plug the above values in the equation for present value of an annuity and a single sum in future:
|$20 million = $1 million ×||1 − (1 + RATE)-20||+||$5 million|
|RATE||(1 + RATE)20|
We need to find the value of RATE that balances the equation. Let’s try 10% annual interest rate. Because there are four quarterly payment per year, the interest rate we enter is 2.5% (=10%/4). At 2.5%, the value of the right side of the above equation is $18,640,517. We know that there is inverse relationship between interest rate and present value. If interest rate rises, the present value falls and vice versa. We need to get $20 million as the solution, so we must reduce the interest rate. Let’s try 9% per annum which translates to 2.25% quarterly interest rate. At 2.25% the value of the right side of the equation is $19,167,795. We know we are getting closer to $20,000,000. We keep trying until we get to 7.50% annual interest rate (and 1.87% quarterly interest rate) which balances the equation.
Instead of going through the hassle, we can use Excel RATE function. We can get the rate by entering the following in any Excel cell “=RATE(20,-1000000,20000000,-5000000). The result we get is the quarterly interest rate which we must multiply by 4, the number of payment periods per year, to get the annual rate.
Written by Obaidullah Jan, ACA, CFA and last modified on