7 Excel functions to help you manage your finances

2024 Author: Malcolm Clapton | [email protected]. Last modified: 2023-12-17 03:44

For Google Docs, these formulas also work.

1. PMT (PMT) - calculates the amount of monthly payments on debts

This will save time when there are several loan offers from different banks and you do not want to contact each one for details.

Let's say a person has moved to a new apartment and decides to renovate it right now. There is no free money left, so he is going to borrow it from the bank.

What data is needed

First, you need to write the formula correctly - in any free cell.

= PMT (rate; nper; ps)

There are three required arguments in parentheses, without which you won't be able to calculate anything:

1. Rate - the interest on the loan offered by the bank. Let it be 9, 5%.
2. Nper - the number of loan payments. Repair is expensive, but not fatal, so let's take it for a year and a half: that's 18 monthly payments.
3. Ps - the amount that is needed to renovate housing. Let's estimate this case at 300,000 rubles.

How to calculate everything

It is necessary to enter the known data into the table, and then print the formula through the "=" sign. We substitute our data for each of the arguments.

Nothing prevents you from simultaneously entering several proposals into the table with different interest rates and loan terms and comparing the conditions. It is not necessary to rewrite the formula each time; you can simply stretch it around the corner.

2. EFFECT - allows you to calculate compound interest

The function is suitable for an investor who chooses bonds for his portfolio and wants to understand what annual yield will actually be obtained.

Russia borrows money through many federal loan bonds (OFZs). Each issue of such securities has a nominal yield, which determines what percentage of the invested amount per annum the investor will receive. For example, for OFZ 26209 they promise the Parameters of federal loan bonds SU26209RMFS5 / Moscow Exchange 7.6%, and for OFZ 26207 even more Parameters of federal loan bonds SU26207RMFS9 / Moscow Exchange - 8.15%.

But if a person does not need money in the near future, then he will not take profit on bonds. And, most likely, he will invest it in the same securities, that is, reinvest. And then the effective yield of bonds will rise. This will happen due to the mechanism of compound interest: profit is accrued not only on the initial investment, but also on subsequent ones.

What data is needed

The calculation formula is pretty simple:

= EFFECT (nominal_rate; num_per)

There are only two variables in it:

1. Nominal_rate is the yield promised by the bond upon issue. These are 7.6% and 8.15% in our example.
2. Number_per - the number of periods in a year when the investor receives profit (in bonds, it is called a coupon).

How to calculate everything

The principle is preserved: we enter the initial data into the table. The nominal yield and the frequency of coupon payments must be published for each bond on the Moscow Exchange in the Instrument Parameters section. Now it's easy to calculate everything:

Just note that bonds are very clever, the investor needs to take into account other factors that affect profitability. For example, the face value of a security is 1,000 rubles, and it is sold for 996 - the real yield will be higher. On the other hand, the investor will also have to pay the accumulated coupon yield - the automatically calculated compensation to the previous owner of the bond. This amount can be equal to 20-30 rubles, which is why the profitability will fall again. One formula is not enough here.

3. XNPV (NETNZ) - calculates the total profit of the investor

Sometimes people accumulate many assets, each of which irregularly brings money: interest on deposits, coupon payments on bonds, dividends from stocks. All instruments have different profits, so it is useful to understand how much comes out in total.

The function allows you to calculate how much money will be returned after a certain time, for example, after four years. This way the owner of the assets will understand whether he can reinvest the income or buy something expensive.

What data is needed

The formula has three components:

= NET (rate, values, dates)

The second and third are clear enough:

2. Values - how much money is spent on investment and how much is returned.

3. Dates - when exactly the funds come or go.

The first component of the formula is the discount rate. Usually money depreciates over time, and the same amount in the future can buy less than it is now. This means that the current 100 rubles are equal to, say, 120 rubles in 2025.

If an investor wants not only to save money, but also to earn, he needs to take into account the gradual depreciation of the currency. There are many ways to do this, but the simplest is to look at the yield on safe bonds: for example, Parameters of federal loan bond SU26234RMFS3 / Moscow Exchange, OFZ 26234 - 4.5%. The point is that the investor is almost guaranteed to make that kind of profit in the future, this is a “risk-free rate”. It makes sense to assess the investment potential with an adjustment for this percentage.

How to calculate everything

With a minus sign, you need to add costs - in our case, the money spent on securities. Next, we will indicate the income that is available for individual investments in advance.

The bottom line is the investor's actual profit after four years, taking into account the discount rate. It is very small, despite 92 thousand investments: for large income, you need to select more risky, but profitable instruments.

4. XIRR (NETWORK) - evaluates the return on investment by money inflows

Usually, any investor has a choice between different financial instruments. Everyone promises some kind of profit, but it is not always clear which is more profitable.

The function helps to compare the profitability if we do not know the percentage per annum in advance. For example, the rate on a bank deposit is 6%. You can invest money there, or you can invest in the business of a friend who promises to pay a floating amount once a quarter, depending on success.

What data is needed

To determine a more advantageous offer, we apply the formula:

= NET (values, dates)

It is enough to know only two variables:

1. Values - how much money the investor will invest and how much he is promised to return.
2. Dates - a schedule of payments by which profits will be paid.

How to calculate everything

Let's say a person invested 100,000 rubles and received four payments, one per quarter. At the end of the year, the investor knows their size and can calculate the yield - more than 40%. This is 37% more profitable than a bank deposit, although it is more risky.

5. RATE - calculates the monthly or annual interest rate on loans

There are situations when a loan is already available, but the interest is not specified. Suppose a person borrowed 100,000 rubles from a friend and promised to return 20,000 a month within six months. The lender may want to know what the rate comes out.

What data is needed

This formula will be useful:

= RATE (nper; plt; ps)

The three variables in it mean the following:

1. Nper - the number of payments. In our example, the loan is six months, that is, there will be six of them.
2. Plt - the amount of payments. Both principal and interest are counted.
3. Ps - the total amount of the loan. In our example, this is 100,000 rubles.

How to calculate everything

You need to enter the values of each variable into its own cell and apply the formula. The main thing is not to forget to put a minus sign in front of the loan amount, because this is money that has gone.

6. PV (PS) - tells you how much money you can borrow

People sometimes make big purchases. For example, they buy cars. They are expensive, and they take auto loans for cars, which are also expensive to maintain. If a person is not ready to give all his salary for monthly payments, then he can estimate in advance what kind of loan will be comfortable.

What data is needed

The formula for calculating the present value is useful:

= PS (rate; nper; plt)

This will require information that is available on the website of any bank:

1. Rate - at what percentage you will have to take money for a purchase. Let's say 9% per annum, or 0.75% per month.
2. Nper - how long it will take to repay the loan. For example, a four-year loan is equal to 48 monthly transfers of funds.
3. Plt - the size of a comfortable payment.

How to calculate everything

Suppose that a person will be able to give from 40 to 50 thousand rubles a month. In this case, two columns are needed: the rate and the term are constant, only the value of the payment changes. As a result, we will see that the car should cost no more than 1, 6 or 2 million rubles.

Cars with such a price will not be dragged into debt. This means that you can reduce your choice space and look for suitable models.

7. NPER (NPER) - helps to calculate the accumulation time

Usually banks explain what percentage a person will receive on their deposit and how much money they will earn. But sometimes the investor has a different goal - to accumulate a specific amount by a specific date. The function will help you calculate this period.

What data is needed

To find out how long it will take to collect money, we use the formula for the number of periods:

= NPER (rate / periods_capitalization; plt; ps; bs)

It consists of four main values and one additional:

1. Rate is the annual interest rate that is offered to the depositor. Let's assume 7%.
2. Capitalization_periods is the number of times a year that the bank calculates interest. This is often done monthly, so we write "12".
3. Pmt - monthly payment. Let's say the contribution is not replenishing, so the indicator will be equal to zero.
4. Ps - the initial amount on the deposit. Let's say 100,000 rubles.
5. Bs - the amount that the depositor intends to receive at the end of the term. For example, 200,000 rubles.

How to calculate everything

A person is going to put 100,000 rubles on a deposit at 7% and wants to take twice as much one day.

To do this, you will have to wait more than two years. Or look for a more profitable investment that will shorten the term.