I have a row which shows date by month and then a row which shows cash flow. I would like a formula to calculate the break even point in the cash flow row and return the month which this occurs. I would also like to show the number of units required to break even.
View 2 RepliesI am looking for a discounted cash flow worksheet where I can set the period of time to a certain number of years, set the terminal value at the end of that period of time, as well as having the ability to set the discount rate. When all three things are set, I would like to see the annual cash flows as well as the present value amount.
View 1 Replies View RelatedI am trying to model the distribution of cash flows from a real estate investment. This is a bit complicated, but basically A1 through A37 are a series of cash flows after payment of debt service from month 0 (closing) to month 36. The real estate project has two investors, the Institutional Investor and the Developer. In this case, the Institutional Investor contributes 90% of the required project equity and the developer contributes the remaining 10%. The cash flow is distributed according to a set of parameters specifying cash flow percentage allocations and IRR targets, collectively known as a "cash flow waterfall"." As with all for-sale residential development deals, the early cash flows are negative and then turn positive as unit sales occur. The order of distribution of the cash flow remaining after debt service is paid is as follows (the waterfall):
1. Both investors receive the return of their invested capital from cash flow distributions.
1. Hurdle 1 (after the return of equity to both investors), the Institutional Investor receives 90% of the monthly cash flows and the Developer 10%, proportional to their respective overall equity contributions, until both investors achieve a 12% IRR (all IRR's to be calculated from deal inception/first dollar invested).
2. Hurdle 2, (after Hurdle 1 is satisfied), the Institutional Investor receives 60% of the monthly cash flows until the Institutional Investor achieves an 18% IRR.
3. The Final Split (after satisfying Hurdle 2) allocates monthly cash flow 50% to each investor.
On an overall project basis, the Developer should realize a disproportionate share of the cash flow after the second hurdle is hit. This is what is known as the "promote." The most recent 36 month (37 including Closing) projected cash flows are as follows:
($1,315,567) - Closing
($113,697) - Month 1
($178,397)
($168,667)
($150,365)
($36,506)
$16,662
$120,283
$107,443
$107,443
I realize this is complicated, but this structure is quite common to real estate, private equity, venture capital and hedge fund investments (along with most any joint venture deals with multiple classes of equity participants). The canned software that is out there that performs this type of anlysis is both expensive and rigid, and doesn't typically enable the required sensitivity analysis in structuring a deal.
I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.
A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...
I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.
Can you create a flow chart in excel? If yes, how?
View 9 Replies View RelatedLooking for a formula that would calculate flow rate (GPM) based of ID of pipe & PSI.
Example - 3/4 inch ID pipe at 70 psi = GPM
Is it at all possible to have a hyperlink that when clicked will take the user to detailed information on another page? I have a summary information with account numbers on one page and would like to click on a hyerlink related to the account number and take the user to the detailed information on another page.
View 3 Replies View RelatedI am using a script that I found on this site (see below). The problem I am having is that I recieve an error on one of my sheets. The used range streatches from Column A to Column J and from row 1 to past 32767. I have found that if I delet the information below row 32767 that I do not get an error. Is there anything I can do out side of splitting the information up on different sheets?
Dim FirstRow As Integer, FirstCol As Integer, _
LastRow As Integer, LastCol As Integer
FirstRow = Cells. Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row
FirstCol = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns).Column
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
Cells(LastRow, LastCol))
theRng.Select
Selection.Copy
i have a river cross section data. for different flow levels in the river i need to to calculate the flow area. i have a very large series of flow level data for 10 years at 1 hr interval.can you give a solution of that.
View 4 Replies View RelatedI am trying to create an interpolation for water flow in a given area. Tables below for better understanding:
Inputs Required
Area
3
km2
L
5
km
L2/A (Calculated)
8.3
[Code] ......
The idea is that when I increase the input area, I would like to interpolate between the RFFP frequency for the flow Q50/Q20 that best fits the area and provide me with a peak flow rate.
I am new to macros on excel but I am trying to create a cash up workbook. This is what I want it to do:
First Sheet should be a summary of the month with each day. From there I want it to generate 4 worksheets for every day's cash up. (which should be Daily Cash up, Credit Card Sales, Cheque Sales & Account Sales).
The Daily Cash up Sheet must pull info from the other 3 and must allow me to input cash values manually. But the cc sales, cheques sales and account sales must be pulled from the other sheets.
After completion of cash up there should be a button on the Daily cash up sheet to say Finalise, where when it is pressed it summarises all totals on the first sheet. So when I open the workbook every morning, there should be a button on the first sheet to create a new 'cash up'.
Then there should also be a button on the first worksheet for month end, so that when you press month end it finalises the month and generates a new work book for the next month.
i am trying to set up a spreadsheet for petty cash so will have a description and an amount on the left of screen and numerous columns on the right for the transactions to be split between ...... Is there any way for me to tell it that if A2=fuel truck then the amount in column B2 needs to also be in column G2?
View 9 Replies View RelatedI want to build a cash register from excel.
I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.
Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).
To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.
How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).
I am building a Discounted Cash Flow.
I have monthly cash flows rolling for 10 years, which I need to sum up into quarterly cash flows.
The amount is $1256,
Cash Denomination will be $500, $100, $50,$10,$5 and $1. I need to split the amount into the number of pieces of each domination to present to the bank when i need to withdraw salary for the workshop workers.
500100501051
1256221011
I'm working on a sheet where I can specify sales by selection of month (Sheetname= ZoekenMaand).
Everything works fine for the months "januari" and "februari" (yes, it's in dutch ) but from "maart" it doesn't seem to work any more.
I have Created an Excel Spreadsheet to work as a EPOS system. The only Problem i am having finishing it off is getting the cash Drawer to open at the end of a sale.
I have my Cash Drawer connected via a Rj 45 in the Back of my Receipt Printer. The Printer i have is a Star Tsp-600. I have Found out that the Open Drawer Codes are 7 or 27,7,10,50,7. I am not entirly sure what they mean though. The code that i have at the moment doesnt work it brings an error "53" file not found. This is my code
i have rec data in which I need a macro that will filter for CASH NOMS and keep everything thats in the criteria but delete everthing else in cash Noms
criteria is named range "Criteria" (I5:K10)
So from this
Sheet1 ABCDEFGHIJKL4 Criteria 5RecTeamAmountRef RecTeamRef 6CASH NOMSAMP1132.36CAETRN CASH NOMS*AMP*.AIMS 7CASH NOMSAMP215.36.AIMS546.3 *WTRN96 8CASH NOMSINTEREST915.12WTRN96 *AMPTRH 9CASH NOMSFA5-65.36AMPTRH *.WSTFGH 10CASH NOMSFA6-9.01CBWSSG *WTWHAT 11CASH NOMSCA-9.01CBWSSG 12CASH NOMSDES-9.01MSFAST 13CASH LIFEUUTU-9.01CBWSSG 14CASH LIFEAMP1-9.01CBWSSG 15CASH LIFEAMP232.12.WSTFGH 16CASH LIFEAMP315975.36CBWSSG 17CASH LIFEAMP4-9.01OPTMOD 18CASH NOMSFA1-9.01CASH WTWHAT Excel tables to the web >> Excel Jeanie HTML 4 .........................
I need to flow data from the Download tab to the Disb08 tab based on the date, that column F of the Download tab = "Outgoing Money Transfer" and that the 4 digit number in row 2 of the Disb08 tab is matched against Column G in the Download tab. When all those match, then the value from column J of the download tab would flow over.
View 2 Replies View RelatedI have got three period like this
Period: 2010 2015 2018
Cash: 100 200 300
I would like to use a function (i am thinking offset will do the work) to put these cashflows on a timeline i.e. 2010,2011,2012 etc...
The three periods described above will be changeable, so the cashflows will adjust accordingly...
With reference to Cash Register example that I downloaded from Roy Cox; is it possible to link the numerical keypad that is created in the file to the PC Keypad.
I realise I would need to use keypress functions but am unaware of the ascii for these keys. Also; how do I limit that the keypad writes it to the label? Otherwise, would I need to create a new userform instead of what was created by Roy.
formula in excel to calculate payback and discounted payback periods with uneven cash flows.
EX.
Yr CF
0 ($2,000)
1 160
2 200
3 350
4 395
5 432
6 440
7 442
8 444
How can I total overtime based on a "Reason Code" and whether the employee chose "Cash" or "Time" compensation?
View 2 Replies View RelatedI have a petty cash sheet set up:
A1 - Date
B1 - Receipt No.
C1 - DEPT CODE
D1 - NOMINAL CODE
E1 - Reason
F1 - Amount
G1 - Running total (I've put a little formula in there to keep the balance - clever eh!)
So - this is fine - except I have to add the total amount for each department/nominal code each month.
What I'm doing at the moment is copying and pasting, sorting the sheet by:
Department
Nominal Code
Date
Then doing a manual add up of each and adding it to my month end report.
I'm really not an expert at all - but I thought there must be an easier way for me to do it. After inputting the petty cash there MUST be a way that it can automatically calculate it?
Or is this going to be a difficult thing to do requiring expertise in pivot tables etc?
The code which you provided works fine no problem for a page break. I need to run the macro for the page break by asking the input file for page break to be done.
For Example, If excel filename "A" contain the code which you have given need to ask to input the filename "B" and process need to be done in file "B".
I have added some code to your code which you provided but it gives error message "1004" "Method 'Range' of object '_Application' failed" at following line :
Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.
Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.
It is non-numeric text, and i've pasted --> values to remove formula issues.
how to break the string into 3 variables.
Example: Given string is : -4.98e-005x^2+0.368x+0.0588
All the string there will be two + symbols.
I have to break the above string into 3 parts and store it into variables like
a=-4.98e-005x^2
b=0.368x
c=0.0588
I need a macro that can take a sheet containing million rows and break it down into multiple sheets within the same workbork beginning on sheet 2 (source data on sheet 1). I would like the macro to include a input box that appears when the macro is run that asks "how many rows per sheet?"
View 2 Replies View RelatedI am herewith enclosing the sample data, which is one row, containing the branch name, branch code, itemcode, rate, qty, amount . Each branch has several items with different quantity of various rates, which has come in the horizontal line. Now I want that in one by one, for converting that data into oracle.
BR.NAME BR.CODEITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTxx1104.5100450153250750263.25158513.5394.5180810506.55003250result should be like thisxx1104.5100450xx2153250750xx3263.25158513.5xx4394.5180810xx5506.55003250
Is there a simple code I can add to the end of my macro that will break all existing links in the active workbook?
View 9 Replies View Related