I need to calculate a pay back period for a certain investment. Excel has options for NPV and IRR but not for the Pay back period. (PBP). I've tried finding a solution using google discussion groups and some books but nothing helped me out. attached you'll find an example of my sheet. I need the PBP to be calculated in e.g. 5,3 years. but when the figures change it should automaticly recalculate the PBP.
View 2 RepliesWhat formula to use to show the Annual ROI for a real estate investment property (single family). I believe I have all my figures to calculate WITH (acquisition amount, annual net income, etc.) -- but I can't figure out how to determine the ROI.
Or - is determining that % even possible/logical?
The way I have this sheet setup is to calculate a 20% fee off the interest earned column "D". Say you earned 6.5% on a beginning balance so the interest earned is in col "D". This works fine for a 20% fee but I need the fee to calculate a 10% fee if the interest in column "C" hit 4% or below and it also needs to be able to calculate a 20% fee if the interested earned is above 4%.
View 5 Replies View RelatedI have a logbook which already uses a number of SUMIF formulas but have been trying to create one so that totals the number of hours flown in the above mentioned periods. I am attempting to copy the formula and then modify for the relevant column in this case: =SUM(IF(FlightDates<(TODAY()-90), 0, FlightLength))
FlightDates is the named range of the calender date in column A and FlightLength is the named range for total flying in column Z. However even though this forumla works in another situation e.g. tracking number of landings in the last 90 days, it comes up with an error.
how i would calculate the number of sickness days and occaisions in the previous 26 week period from todays date.
I've attached the spreadsheet.
So in C4 i want to have the total number of times(occasions) the person has been absent in the previous 26 weeks.
and in E4 i want to have the Total number of Days (taken from E7:E20) the person has been sick in the past 26 weeks
I am looking to calculate compound interest over a period of 10 years.
I am looking at putting a lump sum in at the beginning and contribute in monthly installments for the entire 10 years. How would I go about this.. I know there are formulas there, however I'm not a financial person at all..
Excel formula to calculate period between multiple dates.
Column A has first arrival
Column B has first departure
Column C has second arrival
Column D has second departure
Column E has third arrival
Column F has third departure
Column G has forth arrival
Column H has forth departure
Column I has fifth arrival
I need total period stay from all these columns.
I am creating a timesheet using excel 2003 users enter their shift start/finish time and a break start/finish time. Emplyee's can work night shifts (ie across midnight).
There are penalty rates which apply at different times. I need to be able to work out the amount of worked time that fits into a certain time period. eg. 10pm-7.30am, 7.30am-10pm.
I have a solution based on A clever formula from Daniel Maher that will calculate time within a period. But it doesn't work when the shift goes over two days.
I have attached a spreadsheet to help show the problem .......
I am trying to calculate the average start time for a machine over a period. However the machine start time varies from 10:00 pm to past midnight. I have tried reviewing past posts but cannot seem to find a similar query.
View 13 Replies View RelatedI need also to calculate difference between dates(dd-mm-aaaa hh:mm) in workhours ( hh:mm):
The work period is 9-18 with lunch interval 13-14 The startdate and end date could be out of the work hours and i can't include the extra hours. I can have several days (workdays) at the difference, but i should maintain the format hh:mm.
Ex1:
Startdate 05-03-2013 18:34 ( date to calculation should be 05-03-2013 18:00)
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
will be equal to:
Startdate 06-03-2013 8:34 ( date to calculation sould be 06-03-2013 9:00 )
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
Ex2:
Startdate 06-03-2013 12:01
End date 06-03-2013 14:28
Time Difference 01:29
Column A is the COB Date Column B through N are various stock data. I would like to calculate the trend of correlations over a 4 year period. I can't seem to figure out how to manipulate the data analysis tool pak to do this.
View 3 Replies View RelatedI need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
For example:
I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.
I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.
I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
I have a layout something like the following:
A1
A2
A3
[Code]....
Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).
Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".
Ultimately I am looking to have a matrix which contains all the distances between each location:
A1
A2
A3
B1
[Code].....
I want to easily determine the max value of the stocks I own by only changing the daily value but i cannot bypass the circular reference problem. Example:
Stock|Today's Value| Max Value
A | 1 | 1
B | 3 | 3
Tomorrow i'm going to change today's value and my goal is that the max value remains unaltered if today's value is smaller. like this:
Stock|Today's Value| Max Value
A | 2 | 2
B | 1 | 3
I have a specific problem on irr calculations. In the excel file, I have following data.
date of investment - 1st May 2008
Investment Value - 1000000
Investment close date - 15th Dec 2008
Closure value - 1055000
I want to calculate IRR for the investment for the days the money got invested.
How do I calculate this in excel.
I was looking for how to calculate the value from on cell F11 from
this excel table but my formula didn't work so I'm looking for other solutions?
ur Cell F11 indicates $104
and cell F12 $320
and F13 $658
but from my formula only works with on cell F11
I used this formula on this sheet.
=PMT(C7/12,$A$11*12,,E11)*-1
Enter Retirement Parameters as IndicatedSummary of Retirement CalculationsDate plan began1/1/2008Projected retirement date1/1/2052Number of years to contribute45TRUEAccount value at retirement$542,654Annual contribution$1,300 Total contribution over life of plan$58,500Expected rate of return8%Investment Gain$484,154Type of planRoth IRAPercentage through investment89%$0.00 $1 Contribution NumberDateValue prior to contributionContributionTotal Invested Over Life of PlanInvestment GainValue at end of the year11/1/2008-$1,300 $1,300 $104$1,404$104 21/1/2009$1,300 $2,600 $320$2,920$320 $216 31/1/2010$1,300 $3,900 $536$4,436$658 41/1/2011$1,300 $5,200 $5,200$1,127 51/1/2012$1,300 $6,500 $6,50061/1/2013$1,300 $7,800 $7,80071/1/2014$1,300 $9,100 $9,100
If I want to obtain a future value of $500,000 at the end of 20 years, how much do I need to save each year at an interest rate of 10% per annum? I ended up using the following formula: =PMT(10%,20,-PV(10%,20,,-500000,1),,1). Let's define (Insert/ Name/Define) the answer to this function as Pmt1.
By then using =FV(10%,20,Pmt1,,1) to confirm that Pmt1 will end up providing $500,000 after 20 years I get the answer I am looking for but have absolutely NO IDEA why it works. Worse, I do not know whether it is the correct answer. I have the following function (courtesy of someone) that I use to determine the expected future value of a series of annual payments at a fixed interest rate but also with fixed annual increases in the payments. (Example: $1000 per annum is invested for 20 years. The interest earned on the $1000 is 10% per annum. The $1000 increases by 5% each year - i.e. 19 increases)
=Pmt1* SUMPRODUCT((1+5%)^(ROW(OFFSET($A$1,0,0,20,1))-1),(1+10%)^(20-ROW(OFFSET($A$1,0,0,20,1))+1))
Assuming the payment does not increase, I simply replace the 5% with 0%. When I run this function and use Pmt1 as the annual payment the answer differs from the one that I get from the PMT function that I quoted above until I change the ",,1" in the function to ",,0". What do I not understand about these functions!? Which is correct or are both provided I learn to know what they do? This is the vaguest question I've ever been able to devise simply because I can see that something is amiss and I do not know what - or how to start figuring out what it is that I "know not"!
I am part of an investment sales team and was wondering if the use of specific excel based investment calculators will assist me in my task of advising clients of the best options. Also; if these tools do help, I wanted to create a calculator that displayed the interest schedule depending on the term that its invested for. I have searched and found various threads on this topic; though not too sure how
View 2 Replies View RelatedHow to calculate ANI in one cell? The only way I can currently find the result is to build an amortisation schedule and then divide the sum of the outstandings by the payment frequency.
I'd like to avoid building the amort.
(i) I have a spreadsheet listing all the investments details. For example,
Investment Investment Date Amount
xxx company 1/1/2012 $10,000
yyy company 2/1/2012 $20,000
(ii) Each investments have different investors. For example, xxx company has two investors: A and B; yyy company has 4 investors: B, C, D, E
I want to assign Investors Name and their Personal Investment Amount to the main investment spreadsheet. So that I can retrieve individual investor's investment positions.
For example, I want a spreadsheet showing B's investment. Then this spreadsheet should have details of xxx company and yyy company, as well as B's personal investment amount.
i have a spreadsheet with data that is exported to Excel via our in house investment system, the report looks something like below, though real data consists of 2000 rows of data. Where we have O/S in Bank this means these entries are all physical bank entries i.e statement credit and statement debit, and where we have O/S not in Bank these are all accounting entries, i.e. Ledger Debit and Ledger Debit.
What i am after is a macro that will insert a column next to Team and then input SDR SCR on all statement entries and LDR and LCR on all Ledger entries, the final report should look like the second spreadsheet....
I need to create a sheet that will take payments away from a starting balance. The catch is that I don't know the starting balance. The interest is compounded annually at 3% and each withdrawl is different. My sheet needs to show the interest earned in each year and the account balance like a running sheet, after lets say 5 years there needs to be no money left.
View 2 Replies View RelatedI'm trying to build an investment calculator. Tried searching "calculator" as keyword but return nothing similiar.
There are eight cell : [ A ][ B ][ C ][ D ][ E ][ F ][ G ][h]
[ H ] is price of the leverage
[A] is X balance
[b] is X lot size
[C] is X amount of money use to get x lot
[D] is X percentage of money use to get x lot
[E] is X point gain
[f] is X money gain
[G] is balance + money gain
Ignore the formulae for leverage to determine 1 lot price. Take 1 lot = 250.
What I'm looking for is when I enter any value at cell B, C or D, it will automatically calculate the value for either B, C or D cell.
Let say, the balance is 10000.
If I enter 2 at cell B. Then, it will calculate the value for cell C & D.
If I enter 750 at cell C, then it will calculate value for cell B & D.
If I enter 25 at cell D, it will calculate value for cell B & C.
how to use OR Selection in excel.
i need to share the valve of the job over the months. see attahed excel sheet. say the job worth 10k has a start date of the 30th of nov and the end date is the 1st of dec. the value of the job is shared between two days and then put into the relevent cell. as regards to the months they are not calander months so the start date and end date of the months are diffrent see attached sheet for full details.
View 2 Replies View RelatedI need to find out last occurrence of price (Last Date of Occurrence). Column A contains Date; Column B contains Highest Price of Equity Stock in that date. Reference price (Price we want to look up) is calculated in Cell E1, Cell E2 contains start of evaluation period (date), Cell E3 contains end of evaluation period (date). I want to find out the date on which "last" occurrence of Reference price in Column B "during the evaluation period" was found.
I have tried using array formula
{=MATCH(E1,IF(A2:A21>=E2,IF(A2:A21=E2,IF(A2:A21
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)
Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines
Sheet2 (has current material codes plus current pricing), has about 36000 lines
I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
I have a work book that has about 1200 entries. I'd like to grab everything that comes after the last comma or the last period in the cell. Can this be done?
View 4 Replies View RelatedI have a sheet i am working on at the moment, i basically have in Column A (named Interview Date) a date and in column Q (named Date Cleared) another date.
What i want is so when i enter a date in column Q if the date is 14 days or more after the date in column A it will highlight Blue and if the date is upto 14 days after the date in Column A it will highlght green.
I have two dates in this format:
start: 17.07.2009 11:00:00
end: 17.07.2009 14:00:00
i need to check if the period in between those is during a workday.
our workday starts at 10:00:00 and finishes at 19:00:00
so my expected result is 3 hours. (1400-1100)
other example:
start: 17.07.2009 11:00:00
end: 18.07.2009 11:00:00
result is 1900-1100 + 1100-1000
= 9 hours
because it is during two workdays...
is there a way to calculate this in VBA?
How can I find the total of interest (or and expense) during a given calendar range
Data is as follows
Date Type Amount
4/1/11 Interest 4.00
4/15/11 charge 22.00
5/1/11 interest 3.00
I want a cell to compute the interest in april only.
I have tried =sumif(range, end date, amount_range)- sumif(range, beg date, amount_range)
but how do I nest the type argument into the argument
Possible approaches advanced filter? and function? add another column that uses an if statement to do interest only and do the sumif statement above I was hoping to have a drop down box that could have interest, COGS, beside it and april, may, june above it