0

I have an Excel sheet where I track my financial investments. I have a field where I enter the latest current value of my portfolio, and have a table where I enter deposits I make (date, deposit, current value portfolio, ...).

What I want to calculate is the estimated yearly interest rate I make on these investments. I can't just do (profit/original value)/year or something like that, as extra deposits throughout the year change everything.

If I have $100 invested, and it grows to $150 in almost a year, but the last day I deposit $200 more, I would still want to see 50% yearly interest (or just below it), and not 16% ($50/$300).

So it should calculate the interest a bit as your bank does (but in reverse): taking into account how long the money has been invested.

I have no idea how to do this in Excel. Is this something that can be done with standard formulas, or will I need VBA?

Improve this question
1
  • Take a look at the XIRR function
    – Ron Rosenfeld
    Oct 12, 2020 at 12:10

2 Answers 2

Reset to default
0

After some trying, I think I've found a solution:

In my deposits table I calculate: # days since previous deposit and growth (%) since previous deposit.

I also calculate the number of days since last deposit, and growth (%) since last deposit.

The average growth/day is then:

(growth (%) since last deposit + SUM(growth (%) since previous deposit))/(# of days since last deposit + SUM(# days since previous deposit))

Do this times 365 and I have the average yearly growth %.

Improve this answer
0

You need to use the XIRR Function.

For this, you'll have to get all dates (both for deposits and withdrawals/current value) in one column and all corresponding cashflows in the adjacent column. Something like this:

+------------+----------+
|Date        |Cash flow |
+------------+----------+
|2020-05-12  |      1200|
|2020-07-18  |      1500|
|2020-10-12  |     -2900|
+------------+----------+

Then use the XIRR Function to calculate your returns. As the first argument, specify the values (second column) and the dates as the second argument.

something like

=XIRR(B2:B4, A2:A4)

Improve this answer
3
  • I don't understand how this function helps in my situation. I created a test table with the dates 01/01/2020, 01/01/2021, 01/01/2022 and amounts -100, 106, 112.36 (the initial investment, and the value with a yearly interest of 6%). The function returns 0.71328 (instead of the 0.06 I'm looking for).
    – Coder14
    Oct 12, 2020 at 13:09
  • Coder14 You are not using the formula correctly. Your XIRR function arguments indicates you invested $100; withdrew $106 after a year, and then withdrew another $112.36 at the end of the second year. Should have been: =XIRR({-100;112.63},{43831;44562}) --> ≈6.12% APR. However, since you are not making any payments since the initial investment, XIRR would not be the correct function; you'd just use the RATE function.
    – Ron Rosenfeld
    Oct 12, 2020 at 19:11
  • You said you have extra deposits throughout the year. Hence you need to use XIRR function. If cash flows are irregular, XIRR is the only way to calculate the return. As for the values, you only put the deposits and withdrawals (and the current value as of today, regardless of whether it's withdrawn or not). Only cashflows, not the value of your holdings.
    – gouravkr
    Oct 13, 2020 at 17:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .