Future value FV function:

In the calculation process, we often encounter the problem of how much the principal and interest are after an investment expires.

At this time, you can directly use the FV (final value) in the Excel function to calculate, and you can get the result soon.

The syntax of FV function is: FV(Rate, Nper, Pmt, Pv, Type).

Explain roughly the meaning of these letters:

Interest rate: the interest rate of investment products;

Nper: total investment (or loan) period;

Pmt: the amount of investment (or loan) in each period;

Pv: present value, also known as principal, that is, how much investment;

Type: the number 0 or 1, which specifies whether the payment time starts or ends. If omitted, the value is assumed to be 0.

After knowing the meaning of each letter, just fill in the number in the corresponding position and click OK to get the result.

Give a chestnut:

If Liu Qiangxi invests 1000 yuan in which network in a month, and the rate of return is calculated at 10%, how much money can Liu Qiangxi get after three years?

Here we can see that because the fixed investment is 1000 yuan per month, the interest rate and time are in monthly units.

The formula is written as Fv (10%12,36,-1000,0,0), and the final result is 4 178 1.8 yuan.

Note: Pmt is written as-1000, because this is the amount of monthly expenditure, so "-"is used to indicate expenditure;

Pv represents the initial capital, which is 0 if there is no money in the account;

How much principal do I need to invest?

Current PV function:

PV is generally used to calculate the present value of an investment, that is, how much money you invest now to have M yuan in N years.

The syntax of PV function is PV(Rate, Nper, Pmt, Fv, Type).

Give a chestnut:

Liu Qiangxi's goal is to save 50 thousand yuan in three years (can't afford to buy a house) At present, the annualized rate of return of a wealth management product on a platform is 8%, so how much money does Liu Qiangxi need to invest to ensure the goal is achieved?

The results show that Liu Qiangxi has to invest 396,965,438+0.6 yuan, and it will take three years to get 50,000 yuan, which is a bit difficult. . .

A model friend here will ask, if you want to calculate how much money you invest every month, what should you do?

It's actually quite simple. You can get "∫ 12" on the basis, or directly calculate Pmt. (Note, however, that the time is unified as "month")

Frequent investment,

How to calculate the annualized rate of return?

Internal rate of return formula IRR:

IRR actually represents the internal rate of return, and each investment will generate a series of cash flows accordingly. IRR derives the real interest rate through a series of cash flows.

The grammatical form of IRR function is: IRR (value, guess).

IRR: rate of return;

Values: cash flow;

Guess: estimated value.

There is also a chestnut:

This time, Liu Qiangxi swiped 20,000 yuan with a credit card (the boss is different), and then chose 12 to repay in installments. At that time, the handling fee of the credit card was 0.6%, so the total handling fee to be paid was 20000 * 0.6% *12 =1440 yuan.

Therefore, the repayment of each installment is: 20000/12+1440/12 =1786.67 yuan.

At this time, the credit card amount of 20000 and the repayment per installment 1786.67 form a column, and then the interest rate per installment can be calculated by IRR (see, in the red box).

If you want to get the annual interest rate, just put the monthly interest rate × 12!

Willful investment,

How to calculate annualized income?

The yield formula of discontinuous investment XIRR:

The IRR formula just mentioned is more suitable for regular investment or loan, but in fact, many times our economic behavior is very capricious.

For example, I am very rich this month, er, where to throw a 2000; If pickpocketing is serious next month, which network will you take some money from to invest?

So how to calculate the financial income? Then use XIRR.

The syntax of XIRR function is XIR (value, date, guess).

XIRR: rate of return on irregular investment;

Values: cash flow;

Date: investment time;

Guess: estimated value.

Continue to eat chestnuts:

Liu Qiangxi/kloc-0 bought 3,000 yuan on October 4, 65,438, 3,000 yuan on June 10 and 5,000 yuan on June 6, respectively.

Then the problem is coming. By the end of 10, the total amount of money in the fund account will be 35,000 yuan. What is the rate of return in this case?

The result of this calculation is the final annual interest rate (because it is an irregular investment, it need not be multiplied by 12), which means that Liu Qiangxi's actual rate of return this time is 39. 19%.

Although it looks a bit complicated, it is very convenient to calculate if the data is input accurately.