Compounding Calculations Simplified with Excel
We work to save and invest for our financial goals and then compounding works to help our savings grow with time (Compounding at Work: Start Early). The earlier we start investing, and the more consistently we keep doing it, the more we can collect. The power of compounding is a boon for investors, and the effect of compounding is magnified if
1. The time for which investment is made is longer
2. The rate at which interest is earned is higher
3. The frequency of compounding is more
The interest rates used in compounding are expressed in annual terms. For example, 8% interest indicates that a sum of money invested at 8% will earn this rate once a year.
If interest is calculated once a year, Rs. 10,000 will earn Rs. 800 and become Rs.10,800 after 1 year.
If interest is calculated twice a year, then the frequency of compounding is semi-annual, at half the annual rate. So Rs.10,000 will earn Rs. 400 to become Rs. 10,400 after six months, and Rs. 10,400 will earn 4% to become Rs. 10,816 after six months.
If 8% interest is compounded quarterly, then the frequency of compounding increases to four times a year, at one-fourth the annual rate, and Rs.10,000 will become Rs. 10,824.32.
Effectively, even though the annual interest rate is 8%, the increased frequency of compounding, which is the number of times interest is earned each year (at an annual rate of 8%) increases the effect of compounding and the amount of interest earned. The 8% annual rate of interest effectively becomes more than 8%, working to the benefit of the investor.
To know the effective rate of interest earned for a given annual rate, we can use the MS Excel formula for effective rate =effect(nominal rate, npery).
Nominal rate is the annual rate, and npery is the number of times compounding is done each year
An annual rate of 8% with semi-annual compounding is effectively 8.16%, and with quarterly compounding is effectively 8.24%
· What will the effective rate be with monthly compounding? (Hint: npery will be 12)
Calculations for compounding can be done with ease using MS Excel. We can find a lot of answers if we know a few things:
1. The present sum of money, which is called the Present Value or pv
2. The rate of interest, which is assumed to be an annual rate, and is called rate
3. The number of periods for which compounding is done, which is called nper
4. The future sum of money, which is called Future Value or fv
5. Annual payments being made or received, which is called pmt
· What will Rs.10,000 invested at 8% become with annual compounding after 5 years?
Here, we want to know the Future Value (or fv) of a Present Value (or pv) of Rs. 10,000 at the interest rate (or rate) of 8% after 5 years (or nper).
We use the MS Excel formula for Future Value =fv(rate,nper,pmt,[pv],[type]), and find that Rs.10,000 invested at 8% will become Rs. 14,693 after 5 years with annual compounding.
Here, pv is written as a negative number as we are investing now to receive the future value after 5 years. Type denotes that the sum is invested at the end of the year, and does not earn any interest for that year. We can put 0 here, or leave this blank. The compound interest formula is 10,000X1.08^5
· What if the rate remains 8% but the frequency of compounding is more than annual?
Let us assume quarterly compounding of interest. If we invested at 8% for 5 years, with quarterly compounding, our rate will be 8% divided by 4 (as there are 4 compounding periods each year), and our nper will be 5 year times 4 (20 compounding periods in 5 years). Then we will have Rs.14,859 after 5 years.
· What is the Future Value if we invest Rs.10,000 each year for 5 years, at 8%?
We will still use the MS Excel formula for Future Value =fv(rate,nper,pmt,[pv],[type]), and in place of a single pv of Rs. 10,000, we will have pmt of Rs. 10,000 with a negative sign. We use pmt with a negative sign as we are investing Rs.10,000 each year. This sum will be Rs. 58,666 after 5 years.
· Should we go for an investment of Rs. 100,000 today that returns Rs. 250,000 after 10 years, if interest is 8%?
We again use the MS Excel formula for Future Value =fv(rate,nper,pmt,[pv],[type]), and find that at 8%, 100,000 invested today will be 215,893 after 10 years. If we choose to receive 250,000 after 10 years, we earn more than 8%.
· What is the rate earned if we invest Rs. 100,000 today and get Rs. 250,000 after 10 years?
We will now use the MS Excel formula for Rate =rate(nper,pmt,[pv],fv,[type],[guess]). Since we are to invest Rs. 100,000 once this year, PV will be a negative 100,000, and since we will receive Rs, 250,000 after 10 years, nper will be 10 and FV will be 250,000. Type can be 0 or blank, and guess can be left blank. We find the rate earned is 9.60%.
· How much should we deposit today to have Rs. 1,000,000 after 10 years at 8%? (Hint: use the MS Excel formula for Present Value =pv(rate,nper,pmt,[fv],[type]))
· How much should we deposit each year at 8% to be able to collect a sum of Rs. 1,000,000 after 10 years? (Hint: use the MS Excel formula for pmt =pmt(rate,nper,pv,[fv],[type]))
This article is written to encourage readers to start using MS Excel to make calculations related to investing. This initiation can help us explore the versatile ways in which calculations of varying levels of complexity can be made. With Excel, the simple, fast, and accurate calculations and the ability to do what-if analysis by changing the inputs are very empowering for making investment decisions.
The Excel file below shows the calculations discussed in this article.
Link — Calculating Compounding in Excel
Final Words: Compounding works in favour of the investors, but to the detriment of the borrowers
