Excel 2007 :: Calculating IRR Using Rate Function?
Aug 11, 2012
I am using excel 2007. I am trying to calculate the internal rate of return, without creating an array. I figured out that I can use the rate function to give me the same answer as the IRR function if I have one payment stream of say 36 months @ 1000. Where I am an running into trouble is if I have second payment stream of 6 months extension of 1500. Rate # pay 36 amt 75 PV = 5000 FV 500 , How to handle the the last 6 months ?
This is the situation
asset costs is 5000
monthly lease is 75 for 36 months
extension for 6 months is 125
end of lease buyout is 500
what is the internal rate of return without creating an array.
I want to set up an Excel spread sheet where I can put my total number of sales appointments for the month and the percentage of those I closed. I've done this before, but for the life of me I can't remember the formula I used & I know it's a very simple one! I'm using Office 2007.
Using Microsoft Excel 2007 and all of a sudden, my spreadsheets are not automatically calculating the formulas. It does not matter if I have other workbooks open or not. I still get the problem. It does not happen 100% of the time to make it even more complicated.
- Calulation set to auto in Excel Options.
- No VBA functions being used. I can the worksheet summing 1 + 1 and get the error periodically. It does not have to do with the spreadsheet being too complicated.
- Even if I can hit Ctrl-Alt-F9 to force the formulas to calculate, it won't work.
If I invested $350 per hectare into a project and at the end of 12 years that investment yielded me $150,000, what would my rate of return be over the life of the project?
I'm creating an engineering report and were trying to capture the number of man hrs we are committing to breakdowns the section I'm interested is shown below:-
CraftJob Start TimeJob Finish Time PL RD02.45 03.15
There are two personnel (shown as initials) in the craft cell with the start and finish times of the job. Can I determine the number of personnel by counting the sets of initials i.e. PL + RD =2 then multiply that number by the time worked in this case 30mins (this will vary) so time committed would be 2*30=60mins.
i have an excel 2007 spreadsheet where i keep baseball stats. On the pitching side have a column for the pitchers innings pitch. The data kooks like this:
1.0 1.1 1.2
where the numbers after decimal points represents thirds of an inning. In my spreadsheet i have 4 workheets of different divisions that have a breakdown of each team the team i am keeping stats for plays.
i manually enter the innings pitched in the totals for the season as i cannot figure out how to add these numbers from the individual worksheets to calculate the right number. once the number after the decimal point is greater than two than another full inning is recorded not a continuation of the number.
Example pitcher has already pitched 2.2 innings and he pitches 1.2 innings today making a total of 3.4 which should really be 3.1 one last one pitcher has pitched .2 innings and pitches .1 today making a total of .3 innings pitched but it should be 1.0 innings pitched. proper formula to sum the correct totals in the totals worksheet. i am at a loss and ihave been working on this problem for a little while with no luck.
I'm trying to convert the values entered by users into specific cells into a specific percentage. When I use this macro in Excel 2010, I can enter the value to be calculated directly into the cell and the macro runs automatically. When I open the file in Excel 2007, the macro does not calculate properly unless the value to be calculated is entered into the formula bar.
For example, in Excel 2007, if I enter '30' in cell E11, it should come out as '20%'. Instead it comes out as '0.2%' unless I enter '30' into the formula bar, which then calculates properly. It is far more efficient to be able to enter the value directly into the cell.
I am trying (and failing miserably) to create a fomula to 'score' my pickers in a warehouse environment.
The basic variables are;
1) Units (Total quantity of units on picklist) 2) Lines (Number of different products on picklist) 3) Time (Total time to complete pick)
To give an example, if Units = 8, Lines = 1 and Time = 00:01, I want 'Score' to = 100. (Based on benchmark, rounded down to lowest common denominators)
The added complication I face is the relationship between Lines and Units; it would take someone longer to pick 1 unit from 10 lines than 10 units from 1 line.
I keep coming across bonds having different annual interest rates and different compounding frequencies (quarterly, half yearly and yearly).
I know there is a YIELD function, but it requires so many inputs. I was wondering whether we can calculate cumulative yields just from annual interest rates, compounding frequency and investment duration?
Excel function or macros for calculating the average of waterSD column where the TT column is less than or equal to 100(red markings). functions like IF TT IS LESS THAN OR EQUAL TO 100, THEN CALCULATE AVERAGE OF WATERSD....
I had done manually on the right side in the attached excel sheet. only those yellow markings.
Because already I have some macro which do this process but it is not accounting for the TT column less than 100. it starts from 200 TT values. but i need to include 100 TT also. That's is where now we have yellow markings. I will provide you the macro code if you can edit that where it will start calculating form 100TT value, it will be really great. The code is,
[Code] .....
Attached File : average for watersd OF 100TT.xlsx‎
I have pv, fv, and period. there will be no payments made. I want the interest rate. I do it long hand it works. I do it using the rate function it doesn't! This also doesn't work with nper. Here is a simplified excel example. What am I doing wrong?
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.
I need code for a search box function, that returns the information recorded in a cell for example, "Barcelona" or "London" etc), instead of the location of the cells.
I will need to narrow it down to search only the information in the following columns:
Sheet2 I2:J10932
I am totally new to VBA coding and have stumbled my way through a few things, but everything I have searched for so far has had at least one error when transposing to Excel.
I have never worked on workdays before so I do not know most of the available formula and play around. I googled across and found this one: [URL] .......
Where Bob has given an ARRAY formula.
Following is my layout in A1 to C3 grid (for testing purpose): Start Date11/1/2011HOLIDAYEnd Date11/30/201111/3/2011NETWORKDAYS2011/22/2011 The formula resides in B3.
Is there any other development in 2007+ versions of Excel which can handle 6 working days in a week?
I am using the Index and Match function to lookup for two specific criterias in a different worksheet. So far, it is working well, but it gets complicated. I want to look for the criterias in 6 different worksheets based on what a certain column is saying. Here is an example:
A B C D
[Code].....
So, if the continent is Asia in the column A, I want Excel to look in the Asia worksheet for the city and the venue and return me the contact information. Same, if the continent says Europe, I want it to look in the Europe worksheet or the city and the venue and return me the contact information. So on and so forth.
I currently have a worksheet where I want to input a date (G2) and a rank value (H2) ranging from 1 to 4. The header value (B1:E1) corresponding to the date (A2:A4) and the rank (B2:B4) should be returned to I2 (currently returns #NA).
Using formula: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4),)))
which I found under: Find row, find value, then return column heading
However, the above formula does not seem to work with my date order or recurring data values of 1 to 4 over the 3 rows.
The worksheet layout is as follows:
Date
A
B C D
Date
Rank
Header
[code]....
The return value under Header should be C.
I have reversed the order of the dates and put sequential numbers in B2:B4 as plug variables and the above formula will return the correct Header value but I need the formula to work with the current date order and repeating rank values of 1 to 4 in B2:B4. Do not wish to use VBA.
I get this error : Compile error : Sub or Function not defined. I found out that this might be caused by the solver not being referenced. But, this is fixed now but I still get the same error message.
I'm using Excel 2007 - Windows.
Here is the code :
Sub Macro1() Dim i As Integer Dim j As Integer j = 4 For i = 1 To 4000 If ActiveSheet.Cell(i, 12).Value = 1 Then ActiveSheet.Cell(j, 15).Value = ActiveSheet.Cell(i, 2)
I recently upgraded from Excel 2003 to 2007, and the worksheet change procedure that i have embedded in my worksheet no longer fires when the criteria are met. If and if I fix it to work in 2007, will it still work in 2003?
Here is my procedure:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rngCell As Excel.Range If Not Intersect(Target, Range("C19:R19")) Is Nothing Then For Each rngCell In Intersect(Target, Range("C19:R19")) If rngCell.Value = "BLACK" Then MsgBox "Please select a shading style", , "Shading Style Required" End If Next rngCell End If End Sub
I'm using Excel 2007. When I try to scroll with the mouse wheel, it doesn't do anything. If I hold down the control key and scroll with the mouse, it zooms in and out. So that works fine, but I can't do the basic scrolling up and down the document with the mouse wheel. There is no "Tools, Options" menu in 2007 so I don't even know where to find this type of option. The options available from the Office button are completely different.
I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.
Example:
Results for Row #2 gives: 95 with 3 values picked up under "Rate"
Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"
How can I count the number of these values referencing at the "Rate" like in the functions used.
I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.
I tried COUNTIF function, but allows only field/heading.
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.
I have been trying to work on this excel worksheet for a while. I have give the charge column (tab:data) and the rate chart (matrix type with weight and zone). I want to find the formula that would tell me if the charge is in the rate chart (I know it would use countif), and tell me what the zone and the weight associate with it. Using
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.
