Suppose you are making investment in gold. In January, you bought gold worth $200. In March, you had some extra money. So you bought more gold for $300. In August you bought gold worth $ 50. In December, you need money and you want to sell the gold. You check the market rate of gold in December. The gold you own is worth $700. So total cost of gold is $550 and current price of gold in December is $700. That means you will earn a profit of $150 if you sell your gold. Your profit percentage is 27%. But do you understand how much was the return on investment per annum ?
You have to take into account the different time periods and the different amounts that you invested. There is a very simple way of addressing this problem. Just use the XIRR Function of Excel sheet in your laptop.
The XIRR function is categorized under Excel financial functions. It calculates the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic. It does this by assigning specific dates to each individual cash flow. The main benefit of using the XIRR Excel function is that such unevenly timed cash flows can be accurately modeled. In financial modeling, the XIRR function is useful in determining the value of an investment or understanding the feasibility of a project that does not have regularly periodic cash flows. It helps us understand the rate of return earned on an investment. Hence, it is commonly used in evaluating and choosing between two or more investments.
How to use XIRR function:
Let us take the above example: (Note:The percentage calculations have been rounded off)
- On 14-January-2018, you bought gold worth $200.
- On 3-March-2018, you bought gold worth $300.
- On 30-August-2018, you bought gold worth $50.
- On 23-December-2018, you sold all the above gold for $700. So you have made profit of $700-$550 = $150. Profit percent is (150/550) * 100 = 27%.
- But how much was your actual return on investment per annum ? Open your Excel Sheet and put values as below and use XIRR Function. You have got 34% returns per annum on the investment you made !
| Date | Amount |
| 14-Jan-18 | 200 |
| 03-Mar-18 | 300 |
| 30-Aug-18 | 50 |
| 23-Dec-18 | -700 |
| XIRR | 34% |
So you saw, if you are investing over a long period of time by putting different amounts at different dates, you cannot come to conclusion on your profitability by simple profit percentage calculation. You must use XIRR to calculate annual rate of return. This will give you a precise understanding on how well you invested your money.
No comments:
Post a Comment