In order to calculate the annual IRR the formula should be =IRR(A1:A12)*12). The result is a 25.49% IRR. However, I've seen a formula that calculates the IRR in a different way. In this case, the formula is =(1+IRR(A1:A12))^12-1. In tis case the formula yields a 28.69% IRR. What exactly is the second IRR formula calculating? Is it compounding the monthly returns differently than the first formula? Which calculation is more accurate, and under what conditions would you use the first vs. the second formula (and vice-versa)?
attached the spreadsheet that I need help with in that same thread. Please check out the spreadsheet via: [url]
Here's my issue:
I'm trying to be debt free roughly around 10-11 years years based on my current plan. SO, what I am trying to do is figure out what I can do with my savings at 1, 2, and a 3 % rate of return in a savings account that is compounded monthly after that debt free point. My time frame for results are 5-10-15-and 20 years.
I am trying to set up a budget for my self. Of course I am trying to make it as complicated as possible.
I have done searches online and that is how I found this forum. I am finding amortization formulas, but not what I want
Here is my question.
I have two fields, Mortgage and auto loan
I want to have a field that does a calculation for me. I know following fields as an example making numbers up Interest rate is fixed at 5.5% Amount left on loan is 150,000 Loan is fixed at 30 years with 28 years left
I want to run a formula so when I make a monthly payment of 1000 dollars how much of that goes to interest, how much to principle (not to make this complicated even more, but let say that I have 200 escrowed which I am not sure if that has interest calculated on(I don’t think so as it is property tax?))
Same example for car loan. 5 year loan, 4.5% interest, payment 438 a month.
If i borrow Rs.50000/- at 7.25% interest compounding monthly, repayable in 4 quarterly instalments, when i put it in excel using pmt function for emi quarterly payment and ipmt for interest calculation at the end of the 4th quarter (i.e last instalment) the balance will not become zero it shows a balance of Rs.14.35 its due to interest compounding monthly, is there any formula in excel to overcome it?
In the attached file I have a pivot table created from the data in A:D.
By default the pivot table sums the values for each product (e.g. 18.00 for the sales of Product A). Instead of adding the values for each product I would like to get the pivot table to compound the values for each product. In columns J and K I have shown the results that I would expect when compounding the values of sales and profit for each product. Data for more dates will be added in A:D.
What I am trying to do is to create a formula for the attached spreadsheet - that calculates the daily compounding interest based on the higher rate of the two rates for the first five years then after 5 years the calculation should only be based solely on the blocked rate.
I have created a excel sheet here i want the total interest charged for three months in 3rd mnth interest charged column, if i select 7 mnths term total interest charged for 7 months should come in 7th month interest charged colum, if it is 13 months total interest for 12 months in 12th month interest column and remaining 1 month interest in 13th month interest charged column
I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.
Currently... B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y")) J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))
Example:
If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
I have a spreadhseet with various functions on it and what I am trying to do is this.
Cell E4 returns a >35 or <35 true or false value Cell G4 is either blank or has "Yes" text type into it.
What I am trying to do is get cell F4 to return certain arguments.
E4 = >35 and G4 is blank I want it to state "Email Hiring Manager" E4 = ,35 and G4 is blank I want it to state "Wait"
I have a basic IF formula that returns this =IF(E4>35,"Email Hiring Manager","Wait")
Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of =IF(G4="Yes","Email Agency","Email Hiring Manager")
If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"
Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?
I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.
I need help figuring out an IF formula that would allow me to calculate the tax owed. The tax rates are 20%, 25% and 30% and the full bracket total for 20% is 4,000$ and for 25%, 11,500$.
In D14, I have as a taxable income, 20,000$ and In E14, I would need a IF formula that calculates that... but I would need to copy only one formula down the E column to be used on varying taxable incomes...
Do If ActiveCell.Value Is Value Then ActiveCell.Select Else ActiveCell. Offset(1, 0).Select End If Loop Until ActiveCell.Value Is Value
For some reason when you have a formula in a cell but no data, it says its greater than zero...but because there is no data in that cell, but only a formula, is there anyway to get this code to work.
I am working on a sheet that will have a large range of rows used. There is formulas within a few cells in each row specific to that row. When the user enters data into colum A of the last empty row would there be a way to insert two new rows below that row with formatting and formulas? The toughest part for me has been keeping the totals at the bottom updated. I attached the sheet to help explain if I haven't done a very good job at explaining it.
I am currently having an issue with the formula below showing a zero when there is no data, now i am referencing from this cell to another sheet but even with the IF(ISBLANK inside the formula on the other page it still shows a zero which is messing up what i am trying to do on another page.
how do i add this =LOOKUP((E12+G12),{0,1500,6000,15000,55000;3.4,2.9,2.4,1.9,1.4})%*(E12+G12)+0.2 Into this fomula =IF(AND(E3>0,J3="Yes"),((E3+G3)*3.4%+0.2),0)
