Recovery Drawdown Calculator

[John Caines]

Hello All.
I'm trying to figure out how to make a drawdown recovery calculator.
I've come up with the formatting, outline of the calculator,
and I've got an idea of what some of the formulas might be.
Trouble is I can't see how to enter the formulas into the spreadsheet properly.

I have a spreadsheet here in my dropbox;
https://www.dropbox.com/s/lg3opcaeugbycdo/Recovery-Calc.xlsx?dl=0

Image here
https://imgur.com/a/3U2QFtZ

I'm not to great with formulas,
I can't see how to imbed the formulas into the green filled cells to make this all work
Hope someone can help me out here.
Really just can't work it out.
Maybe even a VB solution would be better?

but I think it can be done with just formulas
Many thanks in advance
John C

 

[John Caines]

Just to Clarify the above;
Someone enters a starting amount in cell H5 (In the Image example £3000)
Then they enter (EITHER) Cell D9, How much is lost in £'s,
OR Cell F9, Lost %
OR Cell H9, How much is left in £'s

So cells H14 (recovery % to get back to breakeven) & H16 (amount to recover in £'s to get back to break even are auto calculated

I re-upload image as I noticed aI formatted a cell wrong
https://imgur.com/a/STIpxpz

If anybody knows how to get this working with formulas that I can protect,
but just leave the yellow cells unlocked to enter data then I'm all ears.

I can't quite work this out.

Hope this is a bit clearer.
I wanted to upload an image with cell references but I'm using excel 64bit, and excel jeanie
wont work with 64bit it seems.
(anyone know how to upload an image with cell references using 64bit excel please tell!

Many thanks again
John C

 

[John Caines]

Just to add,
I said earlier in the original post
"I've come up with the formatting, outline of the calculator,"

I take that back!

I've been trying to figure this out myself and just can't get this working.
If anybody can help me out here with some formulas for this to function I'd be over the moon!

My maths never was too great, hey ho, can't be great at everything!

Yours sincerely
John C

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]

$ Start​

[/td][td]

$3,000.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

$ Lost​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

% Lost​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

% Remain​

[/td][td]

66%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

$ Recovery​

[/td][td="bgcolor:#E5E5E5"]

$1,020.00​

[/td][td]B6: =IF(ISNUMBER(B2), B2, IF(ISNUMBER(B3), B3 * B1, IF(ISNUMBER(B4), (1 - B4) * B1, "")))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

% Recovery​

[/td][td="bgcolor:#E5E5E5"]

52%​

[/td][td]B7: =B6 / (B1 - B6)[/td][/tr]
[/table]

 

[John Caines]

Hi SHG!!!!
Amazing!

It's been ages since I've seen you here (I haven't been online like I used to)..
Really really appreciate your help here!

Let me try your formulas out in my sheet.
(Got family over now at the moment, kegs open;
Desperados Original Tequila Flavoured Beer is in flow) !!!

I'll try it out (if my focus is sill single vision) !

Really amazing to hear from you again SHG
Have a great Christmas by the way.

As a note, I did see a webpage here that done the kind of thing I was looking for well,
Took me ages to find anything on google close to what I was lookin g for, they kept reverting back to pensions!
https://www.babypips.com/tools/gain-loss-percentage-calculator

The good thing about the above link is that it had a selector for chosing initiallly if it was a loss / or a gain.
I might have to design a version 2!

Right, let me see if I can sneek away and try out your formulas.

Yours sincerely
A very grateful, and merry
John C
Thanks again SHG!!!

 

[shg]

You're welcome, John.

 

[John Caines]

Hi SHG!
Thanks again for your reply yesterday, I've recovered now!

I've had a rethink SHG, as I made a few errors, saw ways to improve the calculator, and I've spent a few hrs redesigning a new version this morning.
Again, trouble is I'm not clever enough with formulas to get it working! But I can design ok,, well; kind of...)

Here's an image on image imgur. I've added 'callouts' to reference cell loctions.
https://imgur.com/a/J2xGYeh

I include a dropbox excel sheet link here also;
https://www.dropbox.com/s/ejhw951yzkaepjd/Win-Loss-Calculator.xlsx?dl=0

The main difference SHG is that I've managed to make Cell I6 a 'LIST' drop down menu where a user can select either 'GAIN' or 'LOSS'
This then means that from this selection all the calculations will be either showing recovery data (To get back to break even),
Or profit data.

I know from the screenshot it looks 'Busy' but that's because I spent time to include all the cell references.
I would have used (Excel Jeanie/HTML Maker) ,, but I've got 64bit excel installed so I couldn't add it here, as they aren't 64bit compatible).
I don't think there is a way to do it with 64bit excel? If there is, I'm all ears!

I know it's a big ask, but if you / anyone can help getting this calculator working it would be really really fantastic,,
I know the formulas will be more difficult because of the cell 'I6' with the drop down selector, but it makes the whole sheet work well.

Hope all the above makes sense.
So hard to find something like this on the web. Always seems to be pension data!

Many thanks for all your help.
Hope someone can help me out here

Yours sincerely
John C
(& thanks again SHG for your reply earlier, very much appreciated as always!)

Edit,,, just to add, if you open in dropbox, for some reason dropbox shows the referenced cells differently on the web! You have to download it and you'll see the cell references are the same as the one's shown on the image @ Imgur.
Must be a bug. I might contact dropbox over this

 

[John Caines]

Hello All,
Many thanks again to SHG for his original help here,,,, and the main formula which was;

B6: =IF(ISNUMBER(B2), B2, IF(ISNUMBER(B3), B3 * B1, IF(ISNUMBER(B4), (1 - B4) * B1, "")))

But now seeing as I've reconfigured my spreadsheet and added cells, moved cells etc I'm totally confused!!
Still trying to figure out how to get this working,,, looking at SHG's original code as a starter, to try and work out the math.
My math always has been, well,, crap! trying to get my head around this now,, I think I'm about to explode!

The sheet is here if anyone can attempt to get this off the ground and working.
https://www.dropbox.com/s/ejhw951yzkaepjd/Win-Loss-Calculator.xlsx?dl=0

Image on imur
https://imgur.com/a/J2xGYeh

Basically I've now made the calculator into a GAIN or LOSS calculator now,,,using a list select in cell I6
1. So 1st you choose the amount (For example £3000) in Cell I4
2. You select if it was a GAIN or a LOSS from cell I6
3. You then can choose to imput 1 of 3 options;
£ gained or lost (Cell E10),,, % Gained/Lost (cell G10),,, or finally Ending Balance(£'s) (In Cell I10)

So that's what a user can select,,, he/she only has to actually input into 2 cells and 1 selection GAIN or LOSS from cell I6

===============
All the above then leads onto formulas to auto calculate all the cells in in Column I
I14 Calculates times gain or loss,, so (3x) means 3 times gain
I16 £'s gained or lost
I18 % Gained or Lost
I20 Recovery % to break even (if applicable,, if it was a loss)
I23 Amount in £'s to recover to break even
I25 Ending Balance in £'s

This isn't so easy as I originally thought!
Will be a long formula (IF and OR) I think?
Any help here grately appreciated.
It's completely done my head in, a need another Hot mulled cranberry punch seeing as it's still Christmas!

Yours sincerely
John C