• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Negative Amount (Loss) Adjustment over 8 years

Hi Experts,

This is in continuation to my previous question where an excellent solution was provided by XORLX.
Now I am again in a dilemma to solve the next problem.

I am working with a financial model where I have to adjust losses over the next 8 years.

The loss will be adjusted on the basis of FIFO. This means losses occurred first would be adjusted first.

I have uploaded a sample paper to clarify my question.

Here, I'm looking for a single formula that will give me the "Total Balance Amount" after the loss adjustment.

Hope I have given the proper explanation.

Please help!
 

Attachments

  • Q_(Chandoo).xlsx
    12.8 KB · Views: 10
This is not within my comfort zone, so I appear to have misunderstood the problem!
What I did was the first calculate a balance of the profit/loss account using SUMIFS.
I then used MAXIFS to determine whether the current balance exceeds the maximum achieve to date.
Subtracting gives the balance increase.
Code:
= SUMIFS(ProfitLoss, period, "<="&period)

= MMULT( {1,-1}, MAXIFS( Balance#, period, {"<=";"<"}&period ) )
71371
 
This is not within my comfort zone, so I appear to have misunderstood the problem!
What I did was the first calculate a balance of the profit/loss account using SUMIFS.
I then used MAXIFS to determine whether the current balance exceeds the maximum achieve to date.
Subtracting gives the balance increase.
Code:
= SUMIFS(ProfitLoss, period, "<="&period)

= MMULT( {1,-1}, MAXIFS( Balance#, period, {"<=";"<"}&period ) )
View attachment 71371
Hi Peter

Thanks for your time.
Let me brief in another way.
10-20-15301510-355010

Here I want to adjust negative numbers with positive numbers over next 8 columns.
See the first negative (-20) it should get adjusted with positive numbers (maximum in next 8 columns)

here -20 will get adjusted with positive number in 4th column (30).
And 4th column will have balance 10(30-20).

100-15101510-355010

Now moving on, we have next negative number is -15.
It will first adjusted with next column (10-15) and then balance of -5 will get adjusted with next column (15-5).

100001010-355010
And so on.

please note a negative number can be adjusted over next 8 columns only.

Please refer my provided sheet earlier.
Hope I have explain clearly.

please help.
 
Back
Top