# excel math problem, please help



## MrMike (Sep 30, 2020)

I'm trying to calculate month over month returns and it works for positive numbers but not for negative numbers. From below, you can see that when I was down $1,811, then it increased to $11,660, that is not a negative increase.

My calculation is =SUM((currentMonth-oldMonth)/oldMonth)










Thanks for the help.


----------



## Jimmy (May 19, 2017)

If your first cell top left is A1, in A2 type =IF(A1>0,(A2-A1)/A1,-(A2-A1)/A1)


----------



## MrMike (Sep 30, 2020)

Jimmy said:


> If your first cell top left is A1, in A2 type =IF(A1>0,(A2-A1)/A1,-(A2-A1)/A1)


Thank you so much, works great.... i think. generally the numbers look good even if I can't tell they're exact.

Like, having -$5,544 then going up to -$134, that says its a 97.58% increase??? that doesn't sound right


----------



## Jimmy (May 19, 2017)

I will look at again.


----------



## Jimmy (May 19, 2017)

MrMike said:


> Thank you so much, works great.... i think. generally the numbers look good even if I can't tell they're exact.
> 
> Like, having -$5,544 then going up to -$134, that says its a 97.58% increase??? that doesn't sound right
> View attachment 21558


 Actually I think is ok. If you went from -5544 to 0 it is 100%. But you are only going to -134 so it is 97.58%.


----------



## fireseeker (Jul 24, 2017)

MrMike said:


> I'm trying to calculate month over month returns


Maybe I’m not understanding, but these numbers don’t make sense to me.
Do you really have a month-over-month return of 505%??

I note the first column is called “difference”. Are you comparing changes in difference to each other? 
Don’t you really want to measure the rates of change to the account balance instead?

It looks like in month 1 the account was down by $2,732. If the account held $100,000 to start that would be a decline of 2.7%.
In month 2, the account fell to a loss of $5,035. The month-over-month change there would be $5,035-$2,732/($100,000-$2,732) = 2.3% loss that month.


----------



## MrMike (Sep 30, 2020)

Let me give more context:











fireseeker said:


> Do you really have a month-over-month return of 505%??


Keep in mind this is covid time so I know 500% increase sounds crazy but when i'm at -$134 then the next month is $500, i think 500% is right. From -$100 to 0 is double so 100%. So 5, 100% is 500%. My head hurts 

Like wise, in November I was at -$1,811. so double that to 100%. then adding $1811 7.5 times (so 750%ish) is around $11,660.

I think it is generally good except for June 2020. oh wait, if my negative number to 0 is 100%, then -$5,544 to -$134 would be just under 100%.

Actually, OK, I think this is right. Thanks @Jimmy


----------



## RyanWalker09 (Apr 14, 2021)

I think maybe it'll help you


----------



## MrMike (Sep 30, 2020)

RyanWalker09 said:


> I think maybe it'll help you


Thanks for the video. It was interesting but it didn't address my concern of having negative numbers. Every number was positive in the first 2 columns. I know the percent was negative but not the first 2 numbers.

I think @Jimmy nailed it with the If statement =IF(A1>0,(A2-A1)/A1,-(A2-A1)/A1)


----------



## Covariance (Oct 20, 2020)

If the "Total" column is your portfolio balance at month end and there have been no external cashflows, then the holding period return in percent is End of Month Total divided by Beginning of month total subtract 1. These are not annualized.

For example holding period return for Feb is (49768/52500)-1 = -5.2%

Similarly, your holding period return from Feb 1 to end of July is (53004/52500)-1 = 1%

These are holding period returns, not annualized, and assume no new deposits or withdrawals from the portfolio.

(Note: Edited period to Feb through July after seeing there were additions to principal after July)


----------



## fireseeker (Jul 24, 2017)

MrMike said:


> Let me give more context:
> View attachment 21561
> 
> 
> ...



As @Covariance also points out, you're measuring the wrong thing. In your June 2020 example you do not have a 500% month-over-month gain.

At the beginning of June, the account had a balance of $52,366. At month's end it was up to $53,044.
The gain for that month in $ is the end number minus the start number = $678.
The gain that month is $678/$52,366 = 1.3%.

The denominator should be the total, not the difference.

Also, I think "difference" is throwing you off. It is showing the gain/loss on principal, not the gain/loss over the previous month.


----------



## agent99 (Sep 11, 2013)

When building a spreadsheet to monitor your investment performance, it would be best to try and define just what information you need or would like to know. 

Because you are adding to principal regularly, this makes a month to month comparison more difficult. Personally, I would just look at the monthly return - (Gain or loss for month)/(principal at start of month) as % if you wish, But why do this every month? Why not just do it annually? An annual time weighted rate of return might be of interest?


----------



## cainvest (May 1, 2013)

MrMike said:


> Let me give more context:
> View attachment 21561


Not sure exactly are you trying to figure out.
Are you just trying to calculate monthly performance for a specific investment?
If so, principal amounts should not be included in the percentage calculation.


----------



## MrMike (Sep 30, 2020)

cainvest said:


> Not sure exactly are you trying to figure out.
> Are you just trying to calculate monthly performance for a specific investment?
> If so, principal amounts should not be included in the percentage calculation.


I'm trying to figure out the percent increase/decrease from the month before. It's just a fun number to know, that's all.

"Total" should be renamed "Market Value" if that makes more sense.


----------



## cainvest (May 1, 2013)

MrMike said:


> I'm trying to figure out the percent increase/decrease from the month before. It's just a fun number to know, that's all.


But are you going after percentage change ...

of the investment value (market value - book value)
of the total holding (investment value + principal additions)


----------



## MrMike (Sep 30, 2020)

cainvest said:


> But are you going after percentage change ...
> 
> of the investment value (market value - book value)
> of the total holding (investment value + principal additions)


oh, sorry. Total of both my wife's and my TFSA. The spreadsheet combined the 2 accounts.


----------



## Covariance (Oct 20, 2020)

MrMike said:


> I'm trying to figure out the percent increase/decrease from the month before. It's just a fun number to know, that's all.
> 
> "Total" should be renamed "Market Value" if that makes more sense.


[End Market Value / Beginning Market Value ] - 1


----------



## MrMike (Sep 30, 2020)

Covariance said:


> [End Market Value / Beginning Market Value ] - 1


Thanks and that did work until the EndMarketValue was a negative number. But Jimmy knew the formula with an If statement; =IF(A1>0,(A2-A1)/A1,-(A2-A1)/A1)


----------



## Spudd (Oct 11, 2011)

MrMike said:


> Thanks and that did work until the EndMarketValue was a negative number. But Jimmy knew the formula with an If statement; =IF(A1>0,(A2-A1)/A1,-(A2-A1)/A1)


End Market Value can't be negative, unless you are investing so horribly you're going into debt.


----------



## MrMike (Sep 30, 2020)

Spudd said:


> End Market Value can't be negative, unless you are investing so horribly you're going into debt.


The difference between my principal and the market value was negative. During covid, it did drop, the market dropped lower than my principal (you can see the numbers in the above image a few post ago)... but spoilers, all is well now


----------



## Spudd (Oct 11, 2011)

MrMike said:


> The difference between my principal and the market value was negative. During covid, it did drop, the market dropped lower than my principal (you can see the numbers in the above image a few post ago)... but spoilers, all is well now


The thing is, I think you're wrong. Of course the difference can be negative, but I think you're doing the calcs wrong.

Let's take Feb & Mar of 2020 as an example. 

Feb end you had $49768 and Mar end you had $47465.

Your decrease for the month of March was (47465/49768) - 1 according to @Covariance's formula. Answer: -0.046 or -4.6%. 

Your formula from @Jimmy (before he had the full context) gave you -84%. That -84% is versus the difference from the month before. It means you lost 84% more money than you did the month before. But that is a meaningless number that nobody cares about. What you care about is what percentage of your portfolio did you lose, and for that, you should use @Covariance's formula.


----------



## Covariance (Oct 20, 2020)

By way of example. If we start with $100, and our portfolio appreciates to $110 at the end of the month. We made a 110/100-1 = 10% return that month
Now we have a portfolio worth $110 at the start of the next month. And this month it appreciates to $145. Our return for this month is 145/110-1=32% 
The third month starts with a portfolio value of $145 but unfortunately we end the month at $105. Our return for this month is 105/145-1= -28%
Our monthly returns are
Month 1, 10%
Month 2, 32%
Month 3, -28%

Finally, our holding period return across all three months is 105/100-1=5%


----------



## MrMike (Sep 30, 2020)

Spudd said:


> The thing is, I think you're wrong.


@Spudd @Covariance Yes, you're both 100% right - my math is wrong. The difference is from the principal but that's not the change in market from month to month.

How does this look now:









The Difference column is the difference between my Principal and the Market Value.
The Month over Month column is the % change from the last month BUT minus the increase to the principal. I had to do the minus because when I put in $35K principal, it showed as a 165% increase but that wasn't because of the market. Now the month over month shows the change in the market (capital +/- and dividends).

Does that make sense?


----------



## Covariance (Oct 20, 2020)

The conventional way of handling contributions to capital (ie deposits) is to insert a row when this occurs. Calculate the return to period ending immediately before the contribution. Then add the contribution amount to the ending balance at that point and use this as the beginning balance for the calculation of next period return.

Referring back to my previous example above. Lets say we contribute another $100 at the end of month 3. We already have the return to this point from the calculations above. The new opening balance at start of month 4 is $100+$105=$205. Now if we were to see an appreciation to 235 at end of month 4, our return for month 4 is 235/205-1=14.6%

Because of the contribution we can no longer use the easy formula for return over all four months because it would be "inflated" due to the contribution instead of our actual fund performance. Instead we geometrically link the returns as follows;
(1+Month1 return)(1+month2 return)(1+month3return)(1+month4return)-1
(1.1)(1.32)(.72)(1.15)-1=20% investment return over the four month period


----------

