How to calculate compound interest with the RATE function

Compound interest. The concept is that the interest earned is also calculated at a certain interest rate, which is the rolling interest. For example, with one year as the interest-bearing period, the interest rate of 5%, assuming that the principal of 10,000 yuan in this way is the total amount of principal and interest received in this way is the first year of s, s1 = 10000 * (1 + 5%) the second year, s2 = 10000 * (1 + 5%) * 1 + 5%)...In the fifth year, s=10000*(1+5*) 5 is (1+5%), and the interest rate after 5 years of compound interest is hoped that you are satisfied.

Put the formula into the cell and you're good to go!

1. First open Excel and import the one you want to operate.

2. Then enter the formula =A2*Power(1+B2,C2) to get the cumulative compound interest value of 1000 yuan principal deposit for 10 years.

3. Then enter the formula for the start time of interest and the end time of interest, =a5*power(1+b5,(d5-c5) 365).

4. The final result shows that 100,000 yuan can be withdrawn at an annual interest rate of 10 years.

Calculate interest only? It seems a little difficult, but when I used to do it, I got a big table to do it as an aid, and a single formula can handle so many situations? It's better to use VBA to come quickly!

I don't know if you give points to VBA? Another question is: what was the interest rate between 1997-10-1 and 1997-11-1?

The first type: functions. =fvschedule(a2,l6:l177) (where l6:l177 refers to the interval experienced by the monthly interest rate) must be used as an auxiliary column, and the interval experienced by the monthly interest rate must be selected by itself).

The second type: VBA

If we want to deposit 20,000 yuan in cash in the bank now, and the interest rate of the bank is 7%, then what is the sum of principal and interest (final value) in 5 years? So if we want to get $20,000 in the next 5 years, how much money are we going to put in the bank now (now value)? The following teaches you to use excel to calculate the defeat Xun, I hope it will be useful to you.

Compound interest terminal value calculation

First, open our excel and switch to [Formula] option, and then we select [Formula] under the ribbonFinance

2. Click [FinanceIn the pop-up drop-down menu, find [fv(fv represents the final value of compound interest).

3. If you can't find it directlyFinanceWe can also click on the leftmost [fx insertion functionIn the dialog box that pops up, select a category and select [Financeand select [in the selection functionfvand click OK.

4. In the new dialog box that pops up, (take the above example as an example) inrate box, 5 in the nepr box, and -20000 in the PV box, the other boxes can be left alone. (Rate represents the interest rate of each period of Mori Yan, NEPR represents the number of periods of money deposited, and PV represents the principal amount that we start depositing in the bank, because the money is an expense, not an acquisition, so it is negative).

5. Then click OK or press enter directly, and our results will be calculated.

The present value of compound interest is calculated as this withering royal calculation

The same is true in [FinanceLocate PV (PV stands for Compounded Present Value) in the ribbon and then pop up the dialog boxEnter 5 in rate, 5 for nepr, and 20000 for fv (fv represents the compound interest final value).

7. Then press OK to get the number we want, excel automatically defaults to a negative number in red, indicating that we want to spend so much money.

Notes:If you are worried that the calculation is wrong, you can also use the formula pen to calculate it yourselffv=p▪（1+i）∧n；Compound Interest Present Value Formula:

pv=（1+i）∧-n；(i denotes the interest rate, n denotes the number of periods).

rate function: returns the interest rate of each annuity period.

Syntax: rate (number of periods, investment per period, present value, future value, investment method) example.

Compound interest is calculated with excel.

Here's how:

The formula for compound interest is expressed in excel as follows:

Accrued value (sum of principal and interest):

Principal * power (1 + interest rate, time).

The unit of time is years, and you can directly divide the difference between the two times by 365, for example, the principal is a1, the interest rate is a2, the start time is a3, and the end time is a4, then the formula for the cumulative value is:

a1*power(1+a2,(a4-a3) 365) If only interest needs to be calculated, then the formula for interest is:

Principal * power (1 + interest rate, time) - principal.

The formula for the above example is:

a1*power(1+a2,(a4-a3)/365)-a1

This is actually a question of finding the number of periods for the final value of a known annuity.

Just combine the univariate solution with the fv function.

The steps are as follows: Step 1: Enter 2320 in cell A1, enter in cell B1, enter =fv(b1, d1, a1) in cell C1, and keep D1 blank.

Step 2: Data --- what-if analysis --- univariate solution.

Step 3: Select C1 in the target cell, target value: -80000, and variable cell D1 to confirm. d1 is the result.

There is a slight ambiguity as to whether to save only one month a year or 12 months a year.