你们都已经学会了计算学校复利的公式。复利和单利(Compound and simple interests)是多年来在现实生活中使用的数学应用之一。在我们生活中的某些情况下，我们需要计算单利和复利。例如，当我们以一定的利率向金融公司或朋友借钱时，我们应该知道复利和单利的计算，以免被骗。
除了在纸上计算复利，如果你知道如何在Excel中计算，这将是你专业的额外优势。上式中，P代表本金，R代表利率，n代表总时间。
在这里，我们将学习使用Excel(Excel)计算复利。但在我们开始之前，让我们看一下复利计算中使用的术语。
- 每年或每年复利(Compounded annually or yearly)：在这里，利率每年适用于本金价值。
- 半年或半年复利(Compounded half-yearly or semi-annually)：这里，本金每 6 个月增加一次，即一年两次。要计算半年复利，我们必须将 n 乘以 2 再除以 2。
- 复合季度(Compounded quarterly)：每年有四个季度。在这里，本金每 3 个月增加一次，即一年增加 4 次。要按季度计算复利，我们必须将 n 乘以 4，然后将利率除以 4。
- 月复利(Compounded monthly)：一年有12个月。因此(Therefore)，按月复利是指每月计息一次。因此(Hence)，我们必须将 n 乘以 12 并将利率除以 12。
如何在Excel中计算(Excel)复利(Compound Interest)(CI)
我们将在这里讨论：
- 当利率每年复利时。
- 当利率每半年复利一次时。
- 当利率按季度复利时。
- 当利率按月复利时。
让我们看看Excel(Excel)中的复利计算。
1]在Excel中计算(Excel)每年复利的利息(Interest Compounded Annually)
让我们取一个具有以下值的样本数据：
(Enter)在Excel中(Excel)输入以上数据，写出如下公式：
=B1*(1+B2)^B3
B1、B2 和 B3 是分别表示本金价值、利率和时间的单元格地址。请(Please)正确输入单元格地址，否则会报错。
2]在Excel中(Half-yearly)计算半年复利(Interest)
在这里，我们必须为我们的数据增加一个价值，每年复利一次。如上所述，两个半年构成一整年。因此(Therefore)，半年有 2 个复利期。
- 本金 = 1000
- 利率 = 10%
- 时间 = 5 年
- 每年的复利期 = 2
(Enter)在Excel中(Excel)输入以上数据，写出如下公式：
=B1*(1+(B2/B4))^(B3*B4)
看，我们将利率（B2 单元格中的值）除以 2（B4 单元格中的值）并将时间（B3 单元格中的值）乘以 2（B4 单元格中的值）。
3]在Excel中计算每(Excel)季度(Quarterly)复利的利息(Interest)
在这里，公式保持不变，我们在半年CI的计算中使用了该公式。现在，您只需更改各个单元格中的值。对于季度 CI 计算，将 B4 单元格的值更改为 4。
4]在Excel中计算(Excel)每月复利的利息(Interest Compounded Monthly)
要计算每月复利的利息，请将 B4 单元格的值更改为 12 并使用相同的公式。
就是这样。如果您对Excel(Excel)中的 CI 计算有任何疑问，请告诉我们。
阅读下一篇(Read next)：如何在 Excel 中计算单利(How to Calculate Simple Interest in Excel)。
How to Calculate Compound Interest in Excel
All of you have learned the formula to calculate the compound interest in your school. Compound and simple interests are among the mathematical applications that have been used in real life for years. At certain instances in our life, we need to calculate the simple and compound interests. For example, when we borrow money either from financial companies or from our friend at a certain rate of interest, we should know the calculations of the compound and simple interests to save ourselves from being cheated.
Apart from calculating the compound interest on paper, if you know how to calculate it in Excel, it will be an added advantage to your professionalism. In the above formula, P stands for the principal value, R is the rate of interest, and n is total time.
Here, we will learn to calculate compound interest using Excel. But before we begin, let’s have a look at the terms used in compound interest calculations.
- Compounded annually or yearly: Here, the rate of interest is applied to the principal value every year.
- Compounded half-yearly or semi-annually: Here, the principal value is increased after every 6 months, which means two times a year. To calculate compound interest half-yearly, we have to multiply n by 2 and divide the rate by 2.
- Compounded quarterly: Every year has four quarters. Here, the principal value gets increased after every 3 months, which means 4 times a year. To calculate compound interest quarterly, we have to multiply n by 4 and divide the rate of interest by 4.
- Compounded monthly: There are 12 months in a year. Therefore, compounded monthly means the interest is applied every month. Hence, we have to multiply the n by 12 and divide the rate of interest by 12.
How to calculate Compound Interest (CI) in Excel
We will discuss here:
- When the rate of interest is compounded annually.
- When the rate of interest is compounded semi-annually.
- When the rate of interest is compounded quarterly.
- When the rate of interest is compounded monthly.
Let’s see the calculation of compound interest in Excel.
1] Calculating Interest Compounded Annually in Excel
Let’s take a sample data with the following values:
- P = 1000
- R = 10%
- n = 5 years
Enter the above data in Excel and write the following formula:
=B1*(1+B2)^B3
B1, B2, and B3 are the cell addresses that indicate principal value, rate of interest, and time respectively. Please enter the cell address correctly, otherwise, you will get an error.
2] Calculating Interest Compounded Half-yearly in Excel
Here, we have to add one more value to our data, compounding periods per year. As explained above, two half years make a complete year. Therefore, there are 2 compounding periods in half-yearly.
- Principal = 1000
- Rate of interest = 10%
- Time = 5 years
- Compounding periods per year = 2
Enter the above data in Excel and write the following formula:
=B1*(1+(B2/B4))^(B3*B4)
See, we have divided the rate of interest (value in the B2 cell) by 2 (value in the B4 cell) and multiplied the time (value on the B3 cell) by 2 (value in the B4 cell).
3] Calculating Interest Compounded Quarterly in Excel
Here, the formula remains the same, which we have used in the calculation of CI half-yearly. Now, you just have to change the values in the respective cells. For quarterly CI calculation, change the value of the B4 cell to 4.
4] Calculating Interest Compounded Monthly in Excel
To calculate the interest compounded monthly, change the value of the B4 cell to 12 and use the same formula.
That’s it. Let us know if you have any questions regarding the calculation of CI in Excel.
Read next: How to Calculate Simple Interest in Excel.
