I am trying to put together a spreadhseet that will calculate the future cost of education fee assuming a given inflation rate. I would like the reuslt to be highlihted in a list or a table. The user would only need input current fee, inflation rate and years where fees are payable. The spreadsheet would do the rest. I have laid this our best I can attached.
View 5 RepliesFrom 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 RelatedI'm trying to calculate a date in the future and subtract one day.
The date entered in N38 is 11/09/11.
The term entered in months in C8 is 24.
I want N39 to return the date 24 months in the future minus one day.
So N39 should dispay 11/08/13
How the heck do I do that?
I am trying to create a spreadsheet that will automatically calculate a date in the future.
The future date calculated needs to take the time NOW (as the user completes a field) and determines if this is before MIDNIGHT on the following SUNDAY. If so it calculates the date as a Tuesday 2 weeks on. For example... I request a date now on 1st Jan which is a Thursday. This is before midnight Sunday and so the date calculated would be Tuesday 20th January.
If the time NOW is after MIDNIGHT Sunday and before the next TUESDAY.. ie 0001hrs on the Monday and 2359hrs on the Monday then the date returned should be Tuesday 3 weeks on.. e.g 27th.
It would also need a function that would allow the total number of referrals on a Tuesday to 20 at which point it would overspill and provide a date on the Friday of the same week.. so in the first example it would be Friday 23rd
Is this something that can be achieved with formulas and a set of dates for it to calculate from or do I need to find someone who can do it in VBA for me.
I am working on a sheet right now where I want to put the day a certain number of days in the future but I want to skip only Holidays, or days in a list on another part of the sheet, but count the weekend days.
All the formulas and post I have read is about someone wanting to skip weekends too. I did find a workdays formula where you and pick what you want to be considered as the weekend but I just want to skip holidays.
Example:
Day is Dec-31-2013 7 days in the future is Jan-7-2014
I want it to be since Jan 1 is a Holiday the answer will be Jan-8-2014
I've written the following code. It calculates the future value of required minimum distributions for traditional IRAs. When I enter the function in excel, it drops the last argument. I've tried changing the order of the arguments, and it always drops the last one. The arguments are : marginalTax (marginal tax rate), r (expected return on investment), t (time), begAge (beginning age), RDT (as range -- required distribution table; looks up the percentage required at each age), IRAValueat70 (calcualtes the value of the IRA at 70 years of age)
Function RMD(r As Double, marginalTax As Double, begAge As Integer, t As Integer, RDT As Range, IRAValueat70 As Long)
age = begAge
endAge = begAge + t
Dim holder As Long
Dim RMDnow As Long
Dim RMDall As Long
hat = 70
If endAge < 69 Then
Else
If begAge <= 69 Then
Do While hat < (endAge)
RMDnow = (IRAValue / (Application.VLookup(age, RDT, 2, False)))
RMDnow = (1 - marginalTax) * RMDnow...........................
I am using the same structure to update another column, under the same sub:
If Target.Column = 5 Then '
Select Case Range("d69").Value
Case "CB1W"
ActiveCell.Value = DateAdd("ww", 1, Now())
Case "CB2W"
ActiveCell.Value = DateAdd("ww", 2, Now())
Case "CB3W"
ActiveCell.Value = DateAdd("d", 21, Now())
Case "CB4W"
ActiveCell.Value = DateAdd("d", 28, Now())
Case "CB5W"
ActiveCell.Value = DateAdd("d", 35, Now())
Case "NI"
ActiveCell.Value = ""
Case Else
ActiveCell.Value = ""
End Select
I would like to apply the code to the whole column but if I change Select Case Range("d69").Value to Select Case Range("d2:1000").Value I get a Type Mismatch Error (13) - (on Case "CB1W" ) - column d cells are data validation drop down boxes that get their value form a named ranged :Lead_Status. The column is also formatted as Text. (I applied the code to d69 because I have data already in the other cells which I don't want altered till I have the code working)
I would also greatly appreciate your advice on how to incorporate the WEEKDAY function to exclude weekends for the DateAdd function.
I want to calculate a date that is 28 days in the future. I don't want to exclude any days - However - if the end date falls on a weekend or holiday, I would like to push it out to the next business day.
I currently have the weekends covered, but am stumped on the holidays.
(For weekends, I am using the WEEKDAY function on a hidden sheet, and then the following 3 IF statements:
IF today + 28 = Mon.-Fri., then give me today + 28.
IF today + 28 = Sat., then give me today + 30.
IF today + 28=Sun., then give me today + 29.
I have tried adding an additional IF statement to address a specific holiday - namely, President's Day on 2/18/08, which is a Monday - but it won't add the extra day, because I think my initial IF statement re: Monday being today + 28 is overriding it.
I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume $100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from $100 I get $90, however 10% of $90 is $9 which equals $99.
View 8 Replies View RelatedI'm trying to combine monthly calculations with "today" and with "workdays"
Example:
start date = 01/01/2009
today's date 09/16/2009
formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)
=edate gives me a month but it doesn't skip weekends or calculate beyond today's date
Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me: =DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)
This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th. FYI: I am a n00b..
follows in paragraph 5 - but first, background!
I have a specific formula (received courtesy of some clever person here at Ozgrid (thanks!)) which I use to calculate the Future Value of a series of future payments that increase at a fixed annual rate and earn interest at a fixed rate.
Here it is: =Pmt1* SUMPRODUCT((1+Increase_in_payment)^(ROW( OFFSET($A$1,0,0,Term,1))-1),(1+Return_on_investment)^(Term-ROW(OFFSET($A$1,0,0,Term,1))+1))
(Example: $1000 per annum (Pmt1) is invested for 20 years (Term). The interest earned on the $1000 is 10% per annum (Return_on_investment). The $1000 increases by 5% (Increase_in_payment) each year - i.e. 19 increases - answer: $89,632 (rounded))
This formula assumes that the payment is made at the beginning of the period.
Question: I would like to change the formula to use MONTHLY payments made in advance, and interest earned on a monthly basis.
Because I REALLY do not know what the formula does, maybe I could ask for a detailed explanation thereof - maybe even from the person who supplied it to me (I cannot see who did!) - and then I can start fiddling with it myself if answers do not come.
Two previous posts of mine that dealt with somewhat different issues on the same formula are:
Determine Present Value From Future Value
and
[url]
I need a macro to calculate the cost basis of the inventory. ( Field F3 and F4 in Sheet1)
Please refer to attached sheet....
I want to calculate the subscription rate as follows:- Subscription is fixed at the minimum rate of Rs.200 and is incremented at the rate of Rs.10/- for every Rs.200/- thereafter?
View 2 Replies View Relateduse the “INDEX & MATCH” formula and I thank him very much for that. Now, I have another problem which I think the “IF & ELSE” statement may come into play. I am trying to calculate the cost of a cushion that is governed by it’s thickness.
Example:
ABCDEFGHI
1CushionB/FrontS/SideBorderQuantitySeat
2Feather121251£8.25
3Feather121241£7.32
4
B2 represents 12 inches
C2 represents 12 inches
D2 represents 5 inches
144 represents square foot
The calculation is,
F2=B2*C2/144*D2*£1.65*E2..............
I am trying to keep a running total of cost, here is what I am doing
A B C D E F G H I
Date Books Pencils tax Month Books Pencils Tax Total
1/2/2014 11.50 2.50 .76 January 35.85 13.50 3.44 52.79
1/5/2014 14.50 3.85 .83 February 10.95 1.50 .68 13.13
1/6/2014 9.85 7.15 1.85
2/6/2014 10.95 1.50 .68
I only used Jan and Feb as an example. So as you see on the right side I will have all 12 months for the year and I just want all the totals for each month to auto calculate. I will have between 15 and 30 entries each month.
Some years ago I came across a formulae to calculate Daily Interest on a Building Society Savings account in the UK. I have used this since but find my calculations never work out the same as my BS, although to my advantage! It is =B3*B4/360*DAYS360(B5,B6,TRUE) Where:
B3=Capital
B4=Interest Rate
B5=Starting Date
B6=Finishing Date
For some reason the formulae uses 360/year and not 365/year. Using both still gives wrong answer.
I am trying to calculate a new base pay rate, but I need it make sure it is at least brought up the new minimum and capped at the new max of the range where applicable. The increase is based on 10%
So here are the columns used:
L = Current Base Pay
Q = New Min of the range
S = New Max of the range
U = where I want to calculate a 10% increase of L, but ensuring it is brought up to at least the min (Q) or not over Max (S). In other words if my min is $12 and Max $18 and my new base pay is $16 -- then I am good. However it if is $11.50 I need the formula to return at least $12. And visa versa -- if the new rate would be $18.50, I need it to return no more than $18.
I am trying to calculate APR (Annual Percentage Rate) for a mortgage loan that has a balloon feature. I have tried to the the RATE function but it only gives me the APR for a loan that is ammortized over 30 yrs and paid in 30 years. I need the APR for a loan that is ammortized for 30 years with a baloon in 5 years.
View 9 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 am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.
I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?
sheets.time chart.xlsx
2014 tab- grey column is installer and white is shop employees
profit loss tab- all calculations done here
payroll tab- all cost data here
I am looking to reassess the useful life of existing fixed assets by one/two years.
For example:-
Original cost $1,727.00
Start Date 30/12/2008
Current End of life 29/12/2013
New End of Life date 29/12/2014
5 year - 20%
NBV as at 30/06/2013 $174.12
How can I calculate the new rate, as I can not change the prior financial years depreciation. I am looking for a calculation, to test the assumptions, and see the overall change. This is just one example.
I have a workbook with rental properties containing 2 sheets. 1 sheet with the general details and the rate sets and 1 sheet where I'm trying to calculate the total price for a period of time between 2 dates. I would like the people who use this workbook to simply enter the dates and get a price based on the amount of days within a rate set.
eg: 1 to 7 days is weekly, 8 to 31 is monthly etc. To display this, I'm using: =LOOKUP(H3,{0,7,30,180,365;"daily","weekly","monthly","6 months","yearly"})
These rates are different for each property, and this formula isn't exactly dynamic; it just displays which rate should be used.
Now my idea is to use IF/THEN kind of a formula, but I'm lost on how to do this. The actual rates are on the other sheet in separate rows and columns belonging to the appropriate property. Also, the rates as they are will probably need to be recalculated to a daily number before it can be used for the price calculation, because a month has either 29, 30 or 31 days.
At the moment the daily rate is diplayed as a daily rate
The weekly as a weekly rate
The Monthly as a monthly rate
The 6-Monthly as a Monthly rate (with a minimum of 6 months of course)
And the yearly also as a Monthly rate (with a minimum of 12 Months)
I've attached the sheet as a reference : Property details import.xlsm‎
I currently use goal seek to calculate an interest rate for a loan product. My problem is i would like to have the same function but not through a goal seek. In goal seek i have to set the value i want to achieve but ideally i want it to calculate automatically
I have attached a workbook with details. I use a loan amortization schedule to calculate the interest from parameters set on sheet 1
What I need is a worksheet which will generate fields from a set of variables (similar to a loan amortization schedule).
I don't know how to write the field code to include the annual percentage rate increase based on that additional input variable.
Input variables would be:
starting rate: (ex: 8¢/kilowatt hour)
starting date: (ex: July 2013)
average kw hours/month: (ex: 1062.5 kw hours)
annual rate increase: (ex: 3% *default 0%)
years to display: (ex: 25 years, *shown in months in fields below)
service months per year: (ex: 12 *default 12)
[code].....
So, data fields below the variables would be blank until these are entered.
Assume I would protect the sheet to avoid messing it up.
this would generate a chart showing rates over time from the fields below.
Also it would generate fields in a summary area to show the rate per year for future years.
Secondary, if there were a way to input actual usage and rates from specific months in the past, that would be useful but not part of this forecast model.
The goal of this model is to forecast electrical costs for residential power users compared to independent solar power generation to 25 years forward.
I want to make this because I am skeptical of the advertised savings that solar installers use to convince clients that their return on investment is so high. I believe their data to be greatly inflated projections.
Using this in comparison with other data such as inflation and wages will allow me to get a very good economic picture of the future of independent solar energy generation and its impact on the average home owner.
I have a base rate in A1, the the units in B1 and need a total in C1.
In A3 i have discount rate (%) for units between 0 and 9
In B3 the have the discount rate (%) for units from 9 to 15
In C3 the have the discount rate (%) for units from 9 to 15
In D3 the have the discount rate (%) for units above 16.
How would the formula looklike?
I am trying to calculate earnings based upon hours worked based on 24 hours format (time out - time in = time worked) multiplied by hourly rate. I cannot get the proper calculation. How can I attach a copy of the Excel worksheet?
View 9 Replies View RelatedI have downloaded .csv files with my phone call costs. I want to compare phone companies, so I need to calculate the h:mm:ss amounts to $ based on flagfall and rate per 30 seconds. I can open the .csv files in Excel but I do not know anything about Excel.
View 4 Replies View RelatedI have a situation where I have to determine the required size of a deposit from which constant withdrawals are to be made until the deposit runs out to ZERO. The deposit earns interest at a fized rate, capitalized monthly after each withdrawal had been made.
In my example case, the target month (The actual cell that needs to have a value of ZERO (when the deposit runs out), or the "Range") is found in cell G16. In another situation, it may be found in cell G19 etc, depending on the situation.
It is this changing of the target cell (the one that needs to be ZERO) that has me stumped. I've been thinking of using VLOOKUP (not trying to lead you ;-)) to determine the actual position of the cell that need to have a value of zero but do not know how to build this into the VBA code of Goalseek. This target cell in the Goalseek code should be the cell in column G opposite the figure 1 in column A.
In my example I simply typed in a figure 1 in column A - in the actual spreadsheet the position of this figure is calculated with a formula and its position moves from situation to situation. If the 1 is in cell A19, the target cell, whose value should be ZERO, will then be cell G19..............
I need to develope a macro for Excel to calculate desired profit margin depending on our cost of each item. Here's the scenario.
A1: landing cost
If 0