IRR- XIRR - Annualized Rate Of Return On Single Transaction
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
ADVERTISEMENT
May 8, 2006
Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.
I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.
I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.
NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.
Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0
Scenario:
Looking for Numeric Criterion 1 (one).
Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1
In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.
View 14 Replies
View Related
Oct 4, 2007
what formula to use when my formula does not determine any data (N/A) in a cell in order to count and use count number to determine annualized % change.
State 2002 2003 2004 2005 Annualized Change
GA 36.8% 37.1% 45.5% NA 4.4%
=SUM((C4-B4)+(D4-C4)+(E4-D4))/3 (here is my formula)
View 9 Replies
View Related
Jun 4, 2007
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.
r = 100((Y/X)^(1/n))-1)
So for X = 200, Y = 750, n = 10, we have
r = 100((750/200)^(0.1))-1) = 14.1309%
View 3 Replies
View Related
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
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
View Related
Feb 13, 2014
From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.
View 6 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
Jul 15, 2009
I have an excel file here that has a list of values. What I would like to do is write a formula that finds me the last transaction value of a particular item code. eg. the final list should show :
30100 cost 1.04 (June Value)
30103 cost 1.25 ( Apr- Value)
I am trying to identify each month the last transaction value of a particular item.
View 4 Replies
View Related
Oct 1, 2009
I'm dealing with 2 spread sheets. The first, "Transactions", lists transactions by date, but they are grouped by series. So the 7 series dates are ascending but when you continue to the 8 series it starts at the first date of transaction and ascend to the latest. The second spread sheet, "Sheet1" is an inventory and lists date of last transaction, part #, Part name, sorted ascending by part #. I need a function that will reference the Part # in sheet1 and yeild the date of the last transaction made with that part #. I have not been able to figure it out.
View 24 Replies
View Related
Mar 5, 2007
i want to create a function / macro for this scenario:
if ID123456 has the status open dated 01/05/07 and the user later decided to change the status of ID123456 to closed on 03/05/07. The graph should count the number of closed then lessen the count of open because the previously open ID is already closed.
View 9 Replies
View Related
Jul 20, 2009
How to group a list of cells with sum value shown.
Example,
A1 to A10 = Dates - D/M/Y (May)
A11 to A15 = Dates - D/M/Y (June)
and so on
B1 to B10 = Value for May transactions
B11 to B15 = Value for June transactions
and so on
What I wish to do is, group all May transaction a sum value of B1 to B10 so that it will appears this way (A1 = May ; B1 = Sumvalue). I also wish to have a way to expand and collapse. Something like clicking on a button or something to expand a list of transaction in the month of May. In this case, a list of transaction from A1 to B10 would appear. Clicking the button again will fold the list and will show (A1 - May and B1 - sumvalue)
View 9 Replies
View Related
Nov 21, 2006
Having n issue explaining a statement to a customer. I want to list his incoming payments and invoices in an Excel sheet. So going in order of the statement I'll list the in and outs but I would like a subtotal under each transaction so he can follow easier. Example
$68,920.77
Subtotal $68,920.77
$13,812.43
Subtotal $82,733.20
$12,563.14
Subtotal $95,296.34
$(20,000.00)
Subtotal $75,926.34
Is this possible using the subtotal command? Here is what I'm getting
$68,920.77
$68,920.77 Total $68,920.77
$13,812.43
$13,812.43 Total $13,812.43
$12,563.14
$12,563.14 Total $12,563.14
$(20,000.00)
$(20,000.00) Total $(20,000.00)
View 3 Replies
View Related
Jan 7, 2009
If I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
View 9 Replies
View Related
Dec 22, 2011
if color(white),pattern(6" tile),finish(gloss),width(>36") & height(>98")then return item ordering# different worksheet datadase.
WALL SELECTIONColor NamePatternFinishExisting
Wall Ht.Wall System stops at:
Wall Repair[SELECT COLOR][SELECT PATTERN][FINISH][SELECT HEIGHT][SELECT HEIGHT][REPAIR]Plum
Wall SpecsSoap Dish Wall SpecsBack Wall SpecsExtra
Wall Specs (if needed)Finished Size:[WIDTH]X[HEIGHT][WIDTH]X[HEIGHT][WIDTH]X[HEIGHT][WIDTH]X[HEIGHT]
Quanity:[QTY][QTY][QTY][QTY]Item Ordering #:
View 2 Replies
View Related
May 11, 2012
What macro would convert this:
Excel 2007AB112234Sheet2
To this:
Excel 2007A51627384Sheet2
I need a macro please that works with a table of any size.
View 6 Replies
View Related
Jan 30, 2014
I think I need to link different formulas here and don't want to use a pivot table. The columns run to row 200 and I have 10 separate spreadsheets. I want to create a new file, a master consolidated view
Each row represents 1 client
Column A Contains Yes or No
Column B Contains the Source (i.e Post, Email, Phone)
Column C contains a range of different dates ie when customer made contact
Column D Contains a currency value.
So the question I need to answer is; Return a sum of Column D for those values who's row attributes are Yes, Post and date of current day +5.
In simple terms who can I contact in 5 days time and what is the expected value.
I have added current date in cell E1. F1, F2, F3, contains text Post, Email, Phone.
View 4 Replies
View Related
Apr 29, 2008
Assume A1:A10 filled with the characters A to J.
I'm looking for a way to return the letter "C" when 3 serves as the index_num BUT I do not want to present all 10 characters in the formula.
I would like to use the range A1:A10 =Choose(3,A1:A10) or, if applicable, to name the range as DATA and then use: =Choose(3,DATA)
I tried all I know, including INDIRECT - but found no solution [is there any ?]
As I'm familiar with all workarounds - I will appreciate replies that use CHOOSE.
View 9 Replies
View Related
Jan 5, 2013
I have tried this. actully i am calculating aging for a particular transaction.
This is my needs. if A1 = between or equal to 0-2 B1 = 0-2 days
A1 = between or equal to 3-5 B1 = 3-5 days
A1 = between or equal to 5-10 B1 = 5 to 10 days
A1 = greaterthan 10 B1 = More than 10 days
Acrully am triying this formula =IF(A1<2, "0-2 Days","5-6 Days") if more than 2 scnario i cant...
View 7 Replies
View Related
Oct 24, 2009
I have a form that requests random info. All this information is saved to a worksheet. I would like to give each transaction a reference number. In order to do so I would like each time a new record is saved to add 1 to the previous reference number. Also I would like to start the transaction with a unique identifier "ie. rn1001".
View 4 Replies
View Related
Nov 4, 2009
I'm trying to take the sum of multiple cells and return it to one single cell using the Vlookup
For Example I want to match the ID numbers from one spreadsheet and use the list of codes to return the value of the sum of all matched numbers. So in this example I would want the number 65 in one cell...
View 9 Replies
View Related
May 18, 2007
I have a list of ID's on one sheet that are also located in a large matrix on another sheet.
This macro uses the Find_Range function to find each ID within the matrix and return the column header where the ID was first located.
There is also a line which return a list of ranges, indicating all the places where the ID was found.
What I want is to convert the list of ranges into a list of corresponding column headers (ie row 1 of all columns in the range)
' Number of id's in list
RowCnt = Application.WorksheetFunction. CountA(Columns("A"))
For I = 2 To RowCnt
LookFor = Sheets("ID List").Range("A" & I)
Set InRng = Sheets("Matrix").Cells
Set Found = Find_Range(LookFor, InRng, xlValues, xlWhole)
On Error Resume Next ' If value is not found
Sheets("RateID Count").Range("C" & I) = Sheets("Matrix") _
.Cells(1, Range(Found.Address).Column) ' Return column header
Sheets("ID List").Range("D" & I) = Found.Address(False, False) ' Return cell address or range of addresses
On Error Goto 0
Next I
View 9 Replies
View Related
Jan 3, 2008
I am trying to write a function that returns a single dimension array from inputed data. I want this to work for any data, i.e. a single cell, an array in vba or a range. I am using the "for each" staement. However, when I pass an array in the "for each" returns the same range, rather than the elements of the range. I've played around a bit, and the "for each" does what I want on an range if I am not passing the range to a function. Is it because I am passing the range into a function as a variant? Here is the code for the function.
Public Function CreateSingleDimensionArray(ByVal dataToConvert) As Variant
Dim vHolder As Variant
Dim vArray As Variant
Dim lElementCount As Long
lElementCount = 0
For Each vHolder In dataToConvert
lElementCount = lElementCount + 1
Redim vArray(1 To lElementCount)
vArray(lElementCount) = vHolder
Next vHolder
CreateSingleDimensionArray = vArray
End Function
View 4 Replies
View Related
Mar 19, 2014
Solution to keep track of bank transaction in a budget - both in and out
With:
=SUMPRODUCT(--(Table[[CAT]:[CAT]]=$C5);--(Month(Table[[DATE]:[DATE]])=G$3);--(Table[[IN]:[IN]]))
Table1:
DATE | CAT | IN | OUT
----------------------
The transaction CATegory and Date place the amount from table - column IN to the corresponding cell in my budget.
I would like my bank account to reflect both income an expenses from 2 columns in Table: IN and OUT
Workbook example: [URL] ...........
View 3 Replies
View Related
Apr 2, 2012
I'm working through a large database on sheet 1 that has multiple transactions for each person, what i am trying to do is find the earliest date and the first amount that each person deposited money. The findings will go into a table on sheet 2
This is a dummy data of what i have: Sheet 1 - database....
View 5 Replies
View Related
Apr 26, 2008
I am trying to automatically show the date of the last transaction per part number. I have attached an example. The formula should also consider the data in WH, BRN, and TRNTYP columns
View 4 Replies
View Related
Jul 2, 2014
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
View 1 Replies
View Related
Jul 2, 2014
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,
MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP
(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
View 1 Replies
View Related
