finding the results of changing 4 variables

Hi, I'm working on a 10 year financial forecasting model for an investment 
fund.  The end result is to calculate the IRR.  There are 4 variable inputs I 
would like to change, and observe how the IRR changes as those inputs change. 
 In other words, I would like to play with changing two different fees, the 
fund size, and the expected return, and see how changing those impacts the 
IRR.

Is there a way to do this on one sheet without manually changing all these 
inputs?  I think Scenario or Data Tables might do it, but I'm not sure.

Thank you.
0
Utf
12/8/2009 10:06:23 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
883 Views

Similar Articles

[PageSpeed] 12

You might try this template, available free from the Microsoft Excel 
Templates area:
http://office.microsoft.com/en-us/templates/TC012342021033.aspx?CategoryID=CT101444811033


"irrhelp" wrote:

> Hi, I'm working on a 10 year financial forecasting model for an investment 
> fund.  The end result is to calculate the IRR.  There are 4 variable inputs I 
> would like to change, and observe how the IRR changes as those inputs change. 
>  In other words, I would like to play with changing two different fees, the 
> fund size, and the expected return, and see how changing those impacts the 
> IRR.
> 
> Is there a way to do this on one sheet without manually changing all these 
> inputs?  I think Scenario or Data Tables might do it, but I'm not sure.
> 
> Thank you.
0
Utf
12/8/2009 10:34:01 PM
irrhelp  -

For single-factor sensitivity analysis, where usually you keep three inputs 
at base case and vary the fourth, you could use a one-way data table.

For two-factor sensitivity analysis, where usually you keep two inputs at 
base case and vary the other two, you could use a two-way data table.

The data table approach is described in my free chapter, Sensitivity 
Analysis Using Excel, available from the Chapters page at www.treeplan.com.

An alternative is to use an add-in to automate similar analyses. My SensIt 
add-in may be useful. Another add-in is Palisade's TopRank.

-  Mike
http://www.MikeMiddleton.com



"irrhelp" <irrhelp@discussions.microsoft.com> wrote in message 
news:2D767420-6FAE-463C-A923-48049B2E9352@microsoft.com...
> Hi, I'm working on a 10 year financial forecasting model for an investment
> fund.  The end result is to calculate the IRR.  There are 4 variable 
> inputs I
> would like to change, and observe how the IRR changes as those inputs 
> change.
> In other words, I would like to play with changing two different fees, the
> fund size, and the expected return, and see how changing those impacts the
> IRR.
>
> Is there a way to do this on one sheet without manually changing all these
> inputs?  I think Scenario or Data Tables might do it, but I'm not sure.
>
> Thank you. 

0
Mike
12/9/2009 3:07:19 AM
Reply:

Similar Artilces:

I need to compare 3 numbers and find the one in the middle
I have three numbers in a single row and would like to identify the middle number enter that number in another cell. Example: 1st # 2nd # 3rd # result 628 678 720 678 655 625 700 655 748 720 725 725 is there a function in excel that can do this? Thanks, Eddie Try =LARGE(A1:C1,2) VBA Noo -- VBA Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=56811 fasteddie wrote.....

Find Duplicate names and delate
Dear experts, I have a small doubt could you clarify that??? That is I find duplicate name but I want to delete one name only, if I filter DUPLICATE….. both names are showing… 1. Select the range of data including the header. You need to have headers for these columns 2. From menu Data>Filter>Advanced Filter>Copy to another location 3. In 'copy to' specify the target cell and check 'Unique records only' 4. Click OK will give you the unique list -- Jacob "Find Duplicate names and delate" wrote: > Dear experts, > I have a small ...

Inbox unread total does not change
I have a problem with one Outlook installation in my office. The number on the right of the Inbox, which should reflect the number of unread messages, is the total of all the messages in the Inbox - both unread and read. How do I fix this? >-----Original Message----- >I have a problem with one Outlook installation in my >office. The number on the right of the Inbox, which should >reflect the number of unread messages, is the total of all >the messages in the Inbox - both unread and read. > >How do I fix this? >. >What version of Outlook are you running? ...

Changing References Inadvertently
This is a multi-part message in MIME format. ------=_NextPart_000_0121_01C6BB96.36E602D0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable A friend has an Excel workbook that contains one overall summary = worksheet, and about 20 individual worksheets that each pull data from = the overall summary sheet. She asked me to help troubleshoot some = problems that she was having. What I found was that some of the 3D references had changed. Instead of = looking from the individual sheet to the summary worksheet in the = current workbook, the ...

Finding a Median
I'm trying to write a query that will return a median for various values taken from a previous query. I've seen some suggestions in my searching, but I haven't been able to get them working. They are also all from before 2003 and refer to Access 97 and 2000. Has any functionality been added to 2003 for this? Or is there a non- code-based way to do it? I've seen it suggested to write a code to open the query, sort it, find the total number of records, divide it in half, then seek out the middle record using that value. I'm still very green when it comes to code, though...

Find a Value the first Time It Occurs
I have a row of values that shows the total cumulative number of sales of items by month. Occasionally, there may be no sales in a month for an item so the cumulative value would stay the same for more than one month. I want to select a number in the row the first time it occurs and not select it if it repeats. What are you wanting to do with the info? To return position (column number) of number 1234 within row 2: =MATCH(1234,2:2,0) A formula that signals it's the first occurence: =COUNTIF($A2:A2,A2)=1 This could be used in a helper row, or as a conditional format f...

Printing changes from 2000 to 2003
Documents that were set up in 2000 with printing set to say one page, are now printing to 2 pages or more in 2003 as if the printing set up has not been remembered. Does anyone know of a way to get around this without changing the print set up in every document that is opened? Thanks Shelagh Maybe being more conservative in the page setup would help. (I'd guess that this is more a difference in printers/printer drivers than an excel issue.) Use the settings for the worst printer you can find. Then hope that you found the worst one. Shelagh wrote: > > Documents that were se...

Find/Replace in RichEdit 2.0
I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my CRichEditDoc/View application so that I can use the ITextDocument interface and can do things such as suspend/resume the Redo buffer. Problem is, now the Find/Replace dialogs don't seem to do anything. If I revert back to RichEdit 1.0 they do! What's going on? BUMP! adriangibbons@yahoo.co.uk (Adrian Gibbons) wrote in message news:<34a2acd5.0404130713.123dbaab@posting.google.com>... > I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my > CRichEditDoc/View application...

Changing Computers
I downloaded Money 2003 from the MS site and now want to install it on my new computer. How do I do that? Thanks! Normally you just need to double click the .exe or .msi file -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com especially if it's a UK specific wish. I do not respond to any unsolicited email regarding Money "Aaric" <anonymous@discussi...

Find (but not find)
My program takes a name from sheet3 goes to sheet1 to Find the name. If it cannot find name, how do you do an If/End to Exit Do while or find out if name has been founf? I have "On Error Resume Next" in program. Thanks again for all your help Gordon As ALWAYS, post your code for comments & suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Gordon" <gwelch1938@yahoo.com> wrote in message news:1184612089.486737.144020@n60g2000hse.googlegroups.com... > My program takes a name from sheet3 goes to sheet1 to Find the na...

Changing publisher 2003 document to a pdf
I created a postcard mailer in Pubisher 2003 using a template. The printer said I have to change the format of a PDF so he can open it and print it. I can't seem to do it. When I go to File and Send e-mail, the only option that shows up is sending it as a publisher document. That's because you need third party software to do that. Adobe's Acrobat is the "real deal" but there are several low-cost or free alternatives. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Chris A." <Chris A.@dis...

excel 2007 autofilter change to 2003 autofilter functionality?
Is it possible to make the 2007 autofilter change back to the 2003 autofilter functionality? You have at least one response at one of your other posts. jonnybrovo815 wrote: > > Is it possible to make the 2007 autofilter change back to the 2003 autofilter > functionality? -- Dave Peterson ...

Finding an event
Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button, a new window (in same workbook) opens with a new sheet. My problem is that excel does not seem to have any events for close of window if there are multiple windows in a workbook. Can someone help Peter Peter, That triggers the Workbook_WindowActivate event, you can use that. HTH Bob "Peter Ostermann" wrote in message news:i9m5v8$7bv$02$1@news.t-online.com... Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button...

Upgrading reports from CRM 3.0 to 4.0
Having spent around 10 hours trying to upgrade a custom report from CRM 3.0 to CRM 4.0, I thought I’d post a message to share my pain and the solution! It appears that in CRM 4.0 Microsoft have made a change to the way in which the standard reports retrieve the name of the user running the report. I have been unable to find any documentation about this. If you have a custom report that is based on a standard CRM 3.0 report (i.e. it has the CRM_FullName parameter & UserInfo dataset) and upload it into CRM 4.0 then try to run it, you may get either of the following behaviours • Th...

Find value in a column and insert rows above
The set up looks like this: ColU ColV ColW ColX Y N N N Y N N N N Y N N N N Y N N N Y N N N Y N N N Y N N N Y Y Columns will always be U through X and will always be sorted in this order. I need to find the first Y in each column and insert 2 rows above that row. On the blank row above the first Y, I need to highlight in yellow and put title in the first cell, such as New, Old, Existing, Deleted. Any help would be greatly appreciated. Thanks for your time, Dee If desired, send your file to my address below. I will only look if: 1. You send a copy of this ...

How do I print 4 different postcards on one sheet in Publisher
Our company sends postcards to customers everytime their properties are advertised in the newspaper. There is usually 8 different properties advertised a day. The templet is the same everytime as far as the design of the card. What is different is the description on each and the picture. Currently we are having to print 4 of each card, each day. This works fine as long as there is not a change in the wording of the ad the next time it is ran or a price change. This is getting to be confusing, wasteful and causing mistakes. I have tried to read about mail merge but this doesn't...

cannot find database
I have an excel spreadsheet that is supposed to update a access db. Whenever I try to save the .xls I get an error stating cannot find db. Even when I open the db with access, I get the error and the db opens anyway?????? This only happens on 2 out of 20 pc's and I cannot figure out why???????? Thanks ...

Sum if Condition is Equal in Range Date and find column
I want to make a sum if Range is a week number and if style is Equal to CONC-92 or CONC-45 Week# 49 Week# 50 CONC-92= 27 CONC-92= 30 CONC-45= 27 CONC-45= 30 Datas are in a pivot table and... Pivot table looks like this: Date CONC-92 CONC-45 CONC-92 CONC-45 12/7 5 5 10 10 12/8 2 2 10 10 12/9 5 5 10 10 12/10 5 5 10 10 please help -- Lorenzo Díaz Cad Technician ...

Trapping a NO FIND after a find
I use the code below to store a row number to a variable after a find. I would like to trap a NO FIND if the find is unsuccessfull Any ideas. FSt1 provided the code below Sub macfindrow() dim rn as string dim rng as range dim therow as long rn = inputbox("enter something to find") if rn <> "" then Set rng = nothing Set rng = range("A1:IV65536").Find(what:=rn, _ After:=Range("A1"), _ Lookin:=xlformulas, _ Lookat:=xlpart, _ ...

change default locations for outlook 2000
Hi I am trying to change the default locations for my Outlook. The problem is I have 2 separate logins through windows 98 and have email accounts configured correctly and I get all the mail from both senders sent to the same location. Any ideas / suggestions would be appreciated. Andrew ...

Format only parts of a formula result?
I created a formula composed mostly of text, including a small formula. This formula: ="The products would be $"&ROUND(E28/1000,0)&"K but since new revenues are enabled almost instantly," is intended to produce: The products would be $1335K but since new revenues are enabled almost instantly, Is there a way to BOLD or ITALICIZE the "$1335K" result but leave the other text unbolded? Scott -- sdubose99 ------------------------------------------------------------------------ sdubose99's Profile: http://www.excelforum.com/member.php?action...

how to get the pointer variable address from a dll
hi all, I have a DLL which has a output parameter LPVOID, A application calls this DLL function, the Dll responsibility is to assign a valid pointer to the lpBuffer, Till here it is OK, but once it returns to the Application this lpBuffer becomes NULL. Prototype, __declspec(dllexport) BOOL TRSTMonGetStatus(LPVOID lpBuffer) Please let me know what to do, I am stuck in this. try this __declspec(dllexport) BOOL TRSTMonGetStatus(LPVOID &lpBuffer) AliR. <karunyalakshmi@gmail.com> wrote in message news:1182179607.989434.318540@x35g2000prf.googlegroups.com... > > hi a...

Conditionally Change Print-Area
How Can I conditionally change Print-Area...something like Conditional Formula? Background: I have designed a template for a class-room to update the student's profile. I will distribute this template to every Class-Teacher S/he will the distribute among students. Initially, I have setup 8 columns. Problem I faced while printing is that suppose some class have 5 students some have 8 students or some have 12. How can i automatically setup print-area (include or exclude the unnecessary columns in print-area) Hope I will get some solution for this. Regards, Murtaza I had a similar pr...

limiting results
I hope I can explain this properly. I have a sheet with 100 results but I only want to display the last 4 results on another sheet if there are more than 4 results - so the results displayed would be 97,98,99,100. Anyone have a suggestion? thanks JT On Fri, 9 Dec 2011 20:02:11 -0600, "JT" <grouch_45@hotmail.com> wrote: >I hope I can explain this properly. > >I have a sheet with 100 results but I only want to display the last 4 >results on another sheet if there are more than 4 results - so the results >displayed would be 97,98,99,100. > >Anyone ...

How do I find the out of office reply?
My out of office reply is missing from tools. How can I retrieve it? Out of Office reply <Out of Office reply@discussions.microsoft.com> wrote: > My out of office reply is missing from tools. How can I retrieve it? Unless you are using an Exchange server, you will not have the Out of Office Assistant. See this: http://www.slipstick.com/rules/autoreply.htm -- Brian Tillman ...