It’s no secret that the stock market can be a risky investment. If you’re not careful, you could lose a lot of money in a short period of time. That’s why it’s important to track your investments and keep tabs on your money. In this blog post, we will discuss how to create a portfolio in Excel and track your investments with formulas. We’ll also talk about some tips for staying safe when investing in the stock market.
Money is a touchy subject for many of us, but I’ll let you in on five simple formulas that will help make your finances more organized. With these tools at hand, it’s easy to stay on top of how much cash comes out each month and where all the rest went!
Focusing solely on investments might seem daunting when first starting off – after all there are dozens if not hundreds (or even thousands!) ways one can skin their financial cat… But fear no more because thanks to me, this post provides insight into five different methods which investors often use while tracking closely their personal finances.
Page Contents
how do you calculate a return?
If you started with $1000 and ended with $1100, your return would be 10%. Now if I say that you invested $6997, and now they have become $9137, now tell me the return. That is where we struggle.
There are several ways to calculate a return, but the most common method is to simply take the ending value of your investment and subtract the starting value. This will give you your total gain or loss. You can then divide this number by the starting value to get your percentage return.
Ok, this is the formula to do this in Excel or Google Sheets. The current value divided by the previous value minus one. When you enter that, you get the return which is 10%.
To calculate the return for Day 2, you enter the following formula
=Value on day2 / Value on day1 -1
For our sheet, the formula would be
=D3/D2-1
It will show you that the return on day 2 is 10%. Now, copy/ paste or drag the cell with the formula till the last data value.
If the investment produces $975 on the next day, then your return is -11.36%. This is a percentage formula. The formula remains the same. You can use this formula to calculate how much increment you got on your salary.
CAGR – Compound Annual Growth Rate
The CAGR is a measure of how an investment grows over time. It’s basically the “average” return you earned on your investment over a certain period of time. To calculate the CAGR, you need to know the starting value of your investment, the ending value, and the length of time (in years) that you held the investment.
The current value divided by the starting value to the power 1 divided by the current period, which is 9, minus the starting period, which is 1, minus 1.
This gives us 3.43%
Here’s the formula for calculating CAGR:
CAGR = (Ending Value / Starting Value)^(1/(Current Year – Starting Year)) – l
For our Excel sheet, we can use this formula to calculate the CAGR for our investments
=(B10/B2)^(1/(A10-A2))-1
The CAGR tells us that our investments grew by an average of 3.43% per day over the nine-day period. You can calculate CAGR on a daily, weekly, monthly, or yearly basis as long as the time interval between 2 consecutive periods remains the same.
Portfolio Tracking
Now that we know how to calculate returns, let’s talk about how to track our investment portfolios in Excel. A portfolio is simply a collection of investments. For example, your portfolio of 10 assets might include stocks, bonds, mutual funds, and crypto.
You have invested money in them and you have respective returns. Now you want to calculate the portfolio return.
There are two main ways to track your portfolio in Excel: manually or with the help of personal finance software.
If you choose to track your portfolio manually, you’ll need to create a new sheet for each investment and enter the ticker symbol, name, quantity, the price paid, and current value. You can then use the return formula to calculate your investment’s performance.
When we have the return on each asset, create a list of assets along with the amount invested in them and the corresponding return. Then, multiply the amount invested with the return percentage to get the Absolute Return of each asset.
For example, for Asset 1, the amount invested is $150 and the return is -3%. This means the Absolute Return is -$4.50. Similarly, calculate the Absolute Return for each Asset.
To calculate the Portfolio Return, take the sum of all Absolute Returns and divide it by the sum of all investments.
For example, if the total investment is $11,419 and the total Absolute Return is $891.55, then the Portfolio Return would be 7.81%. This means the total amount invested in all assets increased by 7.81%. Keep in mind, this does not mean that each asset produced a return of 7.81%.
For our Excel sheet, we can use this formula to calculate the Portfolio Return
=E12/B12
EMI Calculator
Another personal finance tool you can create in Excel is an EMI calculator. An EMI, or Equated Monthly Installment, is the amount of money you pay each month towards repaying a loan.
EMI calculators are helpful because they allow you to see how different loan terms (e.g., length of the loan, interest rate) will affect your monthly payments.
To create an EMI calculator in Excel, you’ll need to know the loan amount, interest rate, and length of the loan (in months). With this information, you can use the PMT formula to calculate the monthly payment:
EMI = PMT(interest rate/12, number of months in the loan, the whole loan amount)
For Example,
If you’re taking out a loan for $100,000 at an interest rate of 12% per year (or 1% per month), and you want to repay the loan over the course of 60 months (or five years), your monthly payment (EMI) would be:
=PMT(12%/12, 60, 100000)
This would come out to $2,224.44
For our Excel sheet, we can use this formula to calculate the EMI
=PMT(B2/12,B3,B1)
This would result in a negative number, which means the EMI is an outflow for the loan taken.
Future Value
The final personal finance tool we’ll look at is the Future Value calculator. The Future Value of money is the value of a sum of money at some point in the future, taking into account interest earned.
For example, if you have $100 today and put it in a Mutual Fund and set a SIP of $50 each month for a period of 60 months, that earns a growth rate of 12% per annum, then the Future Value of your investments at the end of 60 months would be:
Future Value =-FV (interest rate/12, number of periods, regular SIP amount, starting amount)
=-FV(12%/12, 60, 50, 100)
This would come out to $4,265.15
For our Excel sheet, we can use this formula to calculate the Future Value
=-FV(B3/12,B4,B2,B1)
Whichever method you choose to track your investment portfolio, Excel is a helpful tool that can make the process easier. By using formulas, you can save yourself time and ensure that your calculations are accurate. personal finance software can also be a useful tool for tracking your investments and calculating various financial metrics.
The stock market is a risky investment, but with the right knowledge and tools, you can minimize those risks. Use these five formulas to track your investments and keep tabs on your money. If you are new to the stock market, read our beginner’s guide to “Understanding Bullish and Bearish Markets“. With this information at your fingertips, you can make informed decisions about your investments and protect your money. Thanks for reading!
Leave a Reply