Automating Xirr
May 7, 2007
I have a very peculiar problem. Each month I have a set of financial products for which I calculate the IRR values using XIRR, as the cash flows are uneven. Imagine this, Product names in ColumnA, the cashflow dates in B, and the cash flows with transaction types in ColumnC and finall D has amounts in negetive and positive. Each month I add new cashflows for each product as rows.
Manually I scroll down to select the range for XIRR (both date and amount)
Offlate this work has gone really high in terms of values, and manually cacluating XIRR takes hell lot of time for all the products (currently 80 odd)
View 9 Replies
ADVERTISEMENT
Jun 9, 2011
I cannot seem to make my code work. I've used application.worksheetfunction.xirr successfully referring to actual spreadsheet cells, e.g.
VB:
VarIRR = Application.WorksheetFunction.Xirr(Range(Cells(5, 3), Cells(Icount, 3)), Range(Cells(5, 2), Cells(Icount, 2)))
but now, when I'm trying to use arrays instead, it's giving me and error "1004 - Unable to get the XIRR property of the worksheetfunction class."
VB:
tempIRR = Application.WorksheetFunction.Xirr(valueArray, dateArray, 0.01)
I do have the analysis toolpak installed, I've ensured that the date range is seen as date, not text. Number of elements in the value array equals the number of elements in the date array, and I do have negative and positive values..
the full sub of what I'm trying to do:
VB:
Sub CalculateIRR()
Dim i As Integer
Dim j As Integer
[Code]....
View 9 Replies
View Related
Mar 7, 2007
I am having trouble with the Xirr function in VBA. The problem revolves around the sensitivity of the solution to the 'initial guess'. specifically the boundary between a +ve and -ve guess. i.e. if the xirr is large and -ve a positive guess will always give me a solution very close to zero, as opposed to the more meaningfull solution say -65%.
To get around this i have constructed the UDF below, which is used as part of a sub, however for some investments i get the type mismatch error (error(13)) even though the arrays are of identical size and the data should be uniform as it is a dump from a database.
Has anyone encountered this 'type mismatch' problem with xirr before? is there a way to access the vba code for xirr so that it can be altered?
Function fkxirr(arrVals, arrDates)
Dim r As Double
Dim Step As Double
Dim tolerance As Double
Dim xir As Double
r = 10
tolerance = 0.000001
Step = 0.1
View 8 Replies
View Related
Jun 5, 2014
I am trying to automate calculation of IRR for a range of scenarios.
I have a bunch of problems with this:
1. On a full cycle basis, the project start dates are not equal which means that in certain scenarios I get 0% return as the first cash flow is zero.
2. On a point forward basis, negative cash flows may start after T0 (i.e. now)
I have circumvented this on NPV basis by using a Sumproduct of individual cashflows, discount factors (full cycle cashflows are inflated to T0) and a proportional period factor (where T
View 1 Replies
View Related
Aug 12, 2008
Is it posssible to use the xirr function with conditions. For example, an array formula that will only calculate xirr if a column meets a certain criteria or if the dates are within a range?
View 9 Replies
View Related
Dec 2, 2009
Here's my problem:
A) This is regarding a real estate transaction.
B) Assume the funds to acquire the investment total $60,000.
C) Assume the purchase date is 1/1/10.
D) Assume the sales price (including costs) would total $76,000.
E) Assume the sales closing date is 4/1/10.
F) What formula function would allow me to show the Annualized Rate of Return on this transaction?
I Know this is something I should have been able to figure out -- but when I use the XIRR function, I end up with a figure of 167% -- and common sense indicates that's just not correct....?
What I need to do is just find a way to show either the IRR or the Annualized Rate of Return for investment consideration purposes. Is it possible to do this based on one single transaction that occurs in a fraction of a year?
View 9 Replies
View Related
Mar 18, 2004
I found a template at microsoftofficeonline.com it's just a bracket. I feel it has some underlying functionality, as they have a template in visio as well, but I can't figure it out.
So, what I'd like to do is have a workbook with say 100 worksheets. Each worksheet will have a person's bracket. Each person will write in all their picks. Their will be a master bracket where the actual winners will go, as the results are posted. When the master bracket is updated, Excel will flip through each worksheet, checking to see if the win in the master bracket matches the individual bracket. Points will be awarded or not.
Can that be done? I've also done a google search and can't get anymore help. I know that the web has multitudes of free versions, which I'd use, the only problem is that people would hesitate to register. doing it in excel, on the office system, there's no need to register, your cpu login takes care of authentication.
View 9 Replies
View Related
Jun 30, 2006
I'm trying to make alot of graphs(400) and I'm not really sure where to start but here with what I've got so far.
Sub macro2()
Dim x As Long, t As Long, y As Long, z As Long
y = 3
z = 5
x = 4
While x < 1000
With Worksheets("cabernet (2)")
t = Cells(x, 1).Value
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("By:Hz"), PlotBy:=xlRows
ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)"
ActiveWindow.Visible = False
ActiveChart.HasTitle = True
ActiveChart.charttitle.Text = " " & t
Windows("complete Favorite Genes.xls").Activate
y = y + 3
z = z + 3
x = x + 4
End With
Wend
End Sub
I don't really know if putting a variable directly into a range works, so far it doesn't.
As you can tell I need a line graph of the range B3:H5, then the next is the those same columns but 3 rows down, so the next graph is at B6:H8. I'd also like to through in a title here, but I may just be playing with fire. The title is located in column A starting at 4 and corrosponds with each graph, so it too goes is increments of 3, i.e., the first title is at 4 the second at 7 the third at 10. Somebody help me out this isn't working very well at all.
View 5 Replies
View Related
Sep 16, 2006
Private Sub Database_Click()
Application. ScreenUpdating = False
masterfile = "S:OfficeMaster File.xls"
Answer = MsgBox("Do You want to export to Final Database?", Buttons:=vbYesNoCancel)
If Answer = vbYes Then
ThisWorkbook.Worksheets(2). Range("q9").Copy
masterfile.Worksheets(1).Range("a4").Paste
ThisWorkbook.Worksheets(2).Range("q9").Copy
masterfile.Worksheets(1).Range("d4").Paste
ThisWorkbook.Worksheets(2).Range("b3").Copy
masterfile.Worksheets(1).Range("b4").Paste
ThisWorkbook.Worksheets(2).Range("b9").Copy
masterfile.Worksheets(1).Range("c4").Paste
ThisWorkbook.Worksheets(2).Range("e9").Copy
masterfile.Worksheets(1).Range("e4").Paste
ThisWorkbook.Worksheets(2).Range("g9").Copy
masterfile.Worksheets(1).Range("f4").Paste
ThisWorkbook.Worksheets(2).Range("i9").Copy
masterfile.Worksheets(1).Range("g4").Paste
End If
End Sub
for some reason, it doesnt work as it said the masterfile.worksheets is not recognized, but as i go to the immediate thingee, i type in ?masterfile, it give me the exact address, i had been working on this thign for hours, and have no idea what i did wrong.
View 3 Replies
View Related
Jul 25, 2008
I have a large number of sheets with data in the following general format:
x ..... y ............ z etc
A Unique value Unique value
A Unique value Unique value
B Unique value Unique value
B Unique value Unique value
C Unique value Unique value
C Unique value Unique value
D Unique value Unique value
D Unique value Unique value
E Unique value Unique value
E Unique value Unique value
In seperate sheets, I want the following:
x y1 y2
A
B
C
D
E
x z1 z2
A
B
C
D
E
And so on for a number of columns - I always want column "x" with another column, however I want the two values for the same value of x to be next to each other, not above one another.
View 11 Replies
View Related
May 6, 2009
Just wondering if any one can help me come up with some code to remove some lap time in my document ....
View 14 Replies
View Related
Dec 11, 2009
The attached workbook is a follow up report that gets run every Friday. There are 1092 cells that contain formulas that reference a file that was created on Wednesday of the same week. eg.
View 5 Replies
View Related
Jun 11, 2007
I am trying to do is have the code display an input box asking the user to type in the file name, or generate this automatically? to an existing folder in a drive
My master file, imports data from another source (excel file) pastes it in the necessary columns at the moment, however the file is run every Monday so the file name is generic, last week was 10723, this week will become 10724 when the data is run next week,
Can I do what I want to? Via either an inputbox for the file name, or by using something so the file name becomes “107 “ & Weeknum(today()-3)
View 9 Replies
View Related
Mar 16, 2008
I have 1 spreadsheet in which I need to calculate the % weight of some numbers. Columns A to G have data I do not need for the calculation.
Column H has the numbers of whose % weight I need to calculate. The problem is that the no. of rows containing the numbers can change from week to week and I am trying to automate the calculation of the % weights.
So I want try to use column G (as a reference) (like go at the bottom of it so I can know how many rows of numbers there are) and then calculate the % weights.
View 9 Replies
View Related
Apr 30, 2008
I am trying to use Excel VBA to conduct a transaction automatically.So far,I managed to get all the required input in.Now,I need to click the "Submit" Button.
I was thinking of using the InternetExlorer.Document.Links(x).Title to search for the link of the "Submit" button and then use the .Navigate method to proceed to the link.
But when I wrote and ran the :
InternetExlorer.Document.Links(x).Title
i get the following mesage:
"Run Time Error 98
A property or method cannot include a reference to a private object ,either as an argument or as a return value "
View 9 Replies
View Related
May 16, 2008
I have a spreadsheet which i work on everyday, basically I have a formula which needs to be copied in the second column depending on the range in the first column.
Ex : one worksheet will have A1:A100, the B column should be copied to B1:B100, if the next one has A1:A200 then B column should be copied to B1:B200, how to do this with VBA?
View 9 Replies
View Related
Mar 4, 2009
My spreadsheet is linked to a stock data web page using the little documented .iqy function. I have set cell A1 to the web page ticker symbol, so that whenever this cell value is changed the data in the spreadsheet is automatically updated.
My problem is in several parts:
1. Read TickerSymbol.csv file which is comprised of just ticker symbols,
2. Insert (TickerSymbol.csv) symbol 1 into cell A1,
3. Save the results to a file c:XXXSymbol1.csv,
4. Insert (TickerSymbol.csv) symbol 2,
5. repeat, repeat
View 9 Replies
View Related
Oct 20, 2006
I have a report which has several tabs of data input which is automated.
There are then several columns of formula which I manually fill down at the minute, is they an easy piece of code to automate this?
The attached example may help, on the tab data source I want to automate the copying down of the formulas in columns D&E.
Can this to be automated by the button on the tab front page?
View 3 Replies
View Related
Jan 23, 2007
I have created a workbook consisting of a 'master sheet' (this sheet is a worksheet in itself but also summarizes the totals from the subsequent worksheet) and subsequent work sheets. I wish to be able to add worksheets as needed while having the 'master sheet' still updating as you add sheets.
View 9 Replies
View Related
Sep 15, 2012
I've been experimenting with automating saving tabs that we use for billing into a .prn file. It works great on my workstation but when I run it on a coworkers workstation the file gets output to the Windows 7 Documents Library, not the workbook's file path. I want it to Save in the same file path that the workbook is saved in already.
Just to clarify I have many sheet tabs, and these are all saved in the .xlsx file. I was previously clicking one by one and doing save as, other file type, prn... but I've automated that by recording a macro and hacking away at it. specifying the workbooks file path correctly? Here's my code, truncated to just show the name of 2 of the tabs:
VB:
Sub EXPORT_ALL()
'
' EXPORT_ALL Macro
' This Macro saves the sheet then exports all tabs as a PRN.
[Code]...
View 2 Replies
View Related
Mar 11, 2014
I have 300 cells filled with around a dozen different names - i.e. the same names crop up multiple times. What I need is automatically creating a list of all the names that appear, ordered by how many times they appear and with the number of times they appear next to them - i.e. ranking the names by their frequency.
View 3 Replies
View Related
Apr 7, 2014
How to automate outlook 2010 calendar and email integration.
I would like to create a button that sends out calendar invites to a large number of recipients with different specific meeting details (date determined by spreadsheet and other details standard). I'd expect that this would populate a specific shared calendar (not my personal calendar) for managing these appointments. It would be good to avoid duplicate correspondence (e.g. via drop-down list or checkbox).
I've created buttons testing a few codes provided in forums, but have not been able to make these work correctly.
Attached is a simple excerpt of the spreadsheet I'm seeking to automate.
Test example.xlsm‎
View 1 Replies
View Related
Feb 12, 2014
I'm currently trying to automate the process of creating Data Validation.
PFA.
I have Grades and Names List.
" GradesList " Range has values "One, Two, Three, Four "
I'm trying to map Names List to GradesList using INDIRECT().
Debug the function DataValidation() to understand the issue better.
View 7 Replies
View Related
Nov 23, 2009
I've data in a sheet (case1) which I would like to paste in a new sheet (case2) in the shown format. Is there any easy way of automating this? I tried recording a macro & tried somehow to automate this task but gave up after a few tries?
View 8 Replies
View Related
Sep 24, 2008
I have a row with 5 numbers, B1:F1.
A1 is my muliplier cell (percentage).
I want all of the above to be variables (no formula in these cells) and controlled by a different cell (G1).
What function would I insert into G1 to be able to control the figures in B1:F1 via A1.
View 9 Replies
View Related
May 25, 2012
i have a validation list in A5 pointing to F6:F, i need the refmask_no to change according to the validation list in A5:A.
So if i choose from List LEADCASH the refmask_no= '100007995' etc
How can i automate the changing of refmask_no?
Sheet1  ABCDEF4Validation ListCorr _acc_no  Mapping 5LEAD CASH ABNAMROrefmask_no = '100007997' Â
Corr_acc_noLead Set6Â Â Â Â '100007995'LEAD CASH7Â Â Â Â '100020741'LEAD CASH ABNAMRO8Â Â Â Â '100020645'LEAD CASH AUDNZNR9Â
View 1 Replies
View Related
Oct 24, 2012
I was wondering if there was anyway to automate this process. Say take all the files in this folder copy visible cells on a specific tab and paste them into a new worksheet/model we already have built. They shouldn't be consolidated rather pasted below the next.
View 9 Replies
View Related
Dec 5, 2008
Is there any way of automating a macro to run on a daily basis?
View 9 Replies
View Related
Mar 31, 2009
I have a huge database of textual files all of which have the same format - space separated text files. I need to find the average of the last column of all these files.
Each file is named uniquely. Is there an stress-free way to set up an automated procedure to go through each of these files automatically and to record the average of the last column along with the name of the file. I attached a few text files - these are daily prices of a few securities. The last column is Volume. I need to find and record the average of this column for each of these files.
The text files are here:
[url]
View 9 Replies
View Related
Aug 8, 2006
I have a census file. All records have a TK number assigned to them. I need to create a new workbook (for each TK number) from a template file and fill in the file with data from all the records belonging to that TK number. I can write this out in English, but I don't know what type of functions/searches/etc. to use to make this work most efficiently.
View 9 Replies
View Related
