Solved

Modification to ROI Formulas

Posted on 2012-03-12
16
245 Views
Last Modified: 2012-03-15
EE Professionals,

I have a very nifty macro that DMille wrote for me that allows you to put in a "Time Horizon", as a number of months and it auto generates the date fields and copies the monthly calculation formulas into each litigimate month.  This is used to allow someone to determine how many months are to be included in a financial analysis to calculate NPV, IRR and BEP.   Here's the challenge. The formulas for NPV and IRR need to be reconfigured to automatically adapt to the number of months selected in the Time Horizon (since it is now dynamic).

Attached is the WS.  The formulas that need modification are in AF3 and AF4 (right now they are fixed at 36 months).

Thank you in advance,

B.
Workbench-v26.xlsm
0
Comment
Question by:Bright01
  • 8
  • 8
16 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37718399
I suppose I already did this for you in this workbook, on a related question. NVP, IRR, and BEP along with the chart are all dynamic ;)

See attached.

Dave
Workbench-v27-r2.xlsm
0
 

Author Comment

by:Bright01
ID: 37718636
Dave,

It's a little more complex........... The dynamic Time Horizon that you created works great. However, the formula modification that is required to adapt to the new, dynamic Horizon, that is set, is in cell AF3, AF4 and AF5:  

SAMPLE OF THE FORMULA IN AF3:

=IF(AA3=0,"",(NPV((1+AC3+AD3)^(1/12)-1,AI3:BR3)+AH3))

The mod. needs to be changing the end point or last month in the new Time Horizon, which is currently static "BR3"  (I had to set it up for 36 months).  When you set up the dynamic Months, the Months can change but I don't know how to get the formula, specifically BR3 (the end point) to automatically reflect the new, dynamic range.

EXAMPLE:

If I change the Time Horizon to 6 months, BR3 in the formula should be AN3.  If I change the Time Horizon to 12 months, BR3 in the formula should actually reflect AT3.  Only at 36 months in the Time Horizon, where your code fires and it gives me the 36 months correctly, is the formula actually correct as BR3.

Make sense?  Easy fix?

B.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37720862
Ok - this is accomplished with 2 range names:

FinOffset=OFFSET(Scenario_Template!$AF3,0,3,1,COUNT(Scenario_Template!$AI3:$XFD3))
Note, you have to be positioned on cell AF3 when this is created.  Note also, the lack of dollar signs before the 3 in $AF3 and $AI3:$XFD3.  That means, when this formula is used on another row, that that row number will change in the formula.

The formula creates an offset 3 columns to the right, 1 row high, and as many wide as data is found from AI to XFD

This is used for the NPV and IRR calc.

The BEPOffset is a little different, as it references the range one row above to get its range:

BEPOffset=OFFSET(Scenario_Template!$AF5,-1,3,1,COUNT(Scenario_Template!$AI4:$XFD4))
This was created while the cursor was sitting on AF5.

After setting a time horizon, if you hit F2 on one of these formulas, then click on the range name, e.g., finOffset, then hit F9, you'll see all the CF's in that range - so you can see for 6 months, there are 6 numbers in there, 24, there would be 24, etc.

When new scenario sheets are created, these range names will be created for that respective sheet as well.

See attached.

Cheers,

Dave
Workbench-v27-r3.xlsm
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:Bright01
ID: 37720987
Dave,

Before I test and integrate;  does this work both for the "new scenarios" (replicas of "Scenario_Template") as well as the "new use cases" - (replicas of use cases within new scenarios)?

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37720996
Yes - but it won't work with scenarios that were created BEFORE this range name was added.

I tested it after having created it in the Scenario_Template tab, then made a copy of the Scenario_Template tab to create Scenario1.  I then added Use Cases and tested that.

Works fine from my end.

Dave
0
 

Author Comment

by:Bright01
ID: 37721306
Brilliant!  

So just as I integrate this..... is the FinOffSet that is created in the Named Range File automatically generated for the Scenario1 Tab, or do I have to put that in as a Named Range addition?

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37721314
Nope.  As long as the name exists in the Scenario_Template tab, then when you create new scenarios, it will get replicated to the new tab for use, there.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37721338
I tried it at the WORKBOOK level scope, and it didn't work the way I'd imagined, so these names are at the Worksheet level, so they get created in Scenario_Template.  When a new scenario is generated, then the new range names are created on the new Scenario# sheet as well.

Dave
0
 

Author Comment

by:Bright01
ID: 37721377
So I don't need to replicate the FinOffSet for Scenario1; just the Scenario_Template....

Got it.

B.
0
 

Author Closing Comment

by:Bright01
ID: 37721708
Great Work Dave.....tough formula.  Testing it going forward; but integrated well.

Thanks again,

B.
0
 

Author Comment

by:Bright01
ID: 37724447
Dave,

When I replicate a use case, I get Excel asking me if I want to use the FinOffset and BEPOffset each time I add one.  Is there a line of code that makes that automatic?

Thank you,

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37725468
Add

Application.DisplayAlerts = False

Before the routine that replicates scenario template does the copy
0
 

Author Comment

by:Bright01
ID: 37725538
Dave,

Thank you!  Works.   Hey Big Congrats on your point standing!  I don't know what you guys win for your work but it should be great.

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37725622
Tks

It's just points so mostly recognition or ego

Tshirts at certain milestones and if you lead for the year some trophy or something

I do it for the learning, community service, comradre, and I guess recognition in that order

I've learned so much it's really made my job easier at work and my first passion/career was in IT and my hope is to retire with capability so I can earn a few and have fun at the same time

Dave
0
 

Author Comment

by:Bright01
ID: 37725686
Well, I would invite you to come visit the East Coast and the Mountains of NC whenever you want and I'll put you and family up.  Within a couple of years of retirement myself.......and really enjoy EE and the personalities.

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37725708
I'm headed to a meeting in Cambridge in June

About the closest but maybe DC in the Fall
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question