7 Excel functions to help you manage your finances
Miscellaneous / / August 01, 2021
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.
In the Telegram channel “Life hacker»Only the best writing about technology, relationships, sports, cinema and more. Subscribe!
In our Pinterest only the best texts about relationships, sports, cinema, health and much more. Subscribe!
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:
- Rate - the interest on the loan offered by the bank. Let it be 9.5%.
- 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.
- 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 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 return he will actually receive.
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 promiseParameters of federal loan bond SU26209RMFS5 / Moscow Exchange 7.6%, and even more for OFZ 26207Parameters of federal loan bond 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 a complex percent: 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; count_per)
There are only two variables in it:
- Nominal_rate is the yield promised by the bond upon issue. This is 7.6% and 8.15% in our example.
- 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 Mosbirge For more information, see Tool Options. 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 may be equal to 20-30 rubles, which is why the profitability will fall again. One formula is not enough here.
3. XNPV (CHESTNZ) - calculates the total profit of the investor
Sometimes people accumulate many assets, each of which irregularly brings money: interest on deposits, payment of coupons on bonds, dividends from shares. 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:
= NETWORK (rate; values; dates)
The second and third are clear enough:
2. Values - how much money was spent on investment and how much is returned.
3. Dates - when exactly funds come or go.
The first component of the formula is the discount rate. Usually money over time depreciate, and the same amount can buy less in the future 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 easiest one is to look at the yield on safe bonds: for exampleParameters 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 total is the actual profit investor 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:
= NETWORK (values; dates)
It is enough to know only two variables:
- Values - how much money the investor will invest and how much he is promised to return.
- Dates - the schedule of payments by which they will pay profit.
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. Let's say if a person took on credit 100,000 rubles from a friend and promised to return 20,000 monthly 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:
- Nper - the number of payments. In our example, the loan is six months, that is, there will be six of them.
- Plt - the amount of payments. Both principal and interest are counted.
- 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 for cars they take car loan, which is 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:
- Rate - at what percentage you will have to take money for a purchase. Let's say 9% per annum, or 0.75% per month.
- Nper - how long it will take to repay the loan. For example, a four-year loan is equal to 48 monthly transfers of funds.
- 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 they 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:
- Rate - the annual percentage rate that is offered depositor. Let's assume 7%.
- Capitalization_Periods is the number of times a year that the bank calculates interest. This is often done monthly, so we write "12".
- Pmt - monthly payment. Let's say the contribution is not replenishing, so the indicator will be equal to zero.
- Ps - the initial amount on the deposit. Let's say 100,000 rubles.
- 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.
Read also🧐
- How to keep a budget if you have a fickle income
- 20 useful Google Sheets templates for all occasions
- 10 cool apps to take control of your finances
Scientists talk about dozens of COVID-19 symptoms that can persist for more than 6 months
Scientists have named the characteristic symptoms of the delta strain of coronavirus. They are different from the usual COVID-19
The authoritative scientific journal Nature wrote about the safety and effectiveness of "Sputnik V"