A B C D 1 Date code price 2 10/09/08 abc101 1100.00 3 12/10/09 abc104 1255.00 4 30/08/09 abc101 1389.00
In D1 I want the last purchased rate of a code ( suppose here it is abc101, but it would be different for other item). Here Date is dd-mm-yy.
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.
r = 100((Y/X)^(1/n))-1)
So for X = 200, Y = 750, n = 10, we have
r = 100((750/200)^(0.1))-1) = 14.1309%
I have a table that has 3 columns:
Product name (one of 15 products I have)
Purchase Date (converted to excel datevalue).
Week number (a number between 1 and 52)
I'm trying to figure out how to sum the products purchased in a given week.
Would like to enter a formula in column B of Table 1 (on attached file) that would identify if that customer spent $90 or more (on any one purchaese) for any of their 4-5 purchases from Table 2. Expected results have been entered in Column B of Table 1.
View 3 Replies View RelatedI have account numbers in column A where the customers have purchased at least 1 accessory, where the accessory numbers are in column B. If they have purchased more than 1, their account number will be duplicated in column A. I would like to represent the data so that the account number is unique in column A, accessory 1 in column B, accessory 2 in column C and accessory 3 in column D.
Is there any way of doing this?
I'm open to suggestions in MS Access.
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.
View 6 Replies View RelatedI 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?
View 2 Replies View RelatedI have to pay 190000 $ in 180 months. I need a formula to establish an incresing rate for every month. I have a simulation on a paperwork, but I can't figure out the formula. On the paperwork the first rate in the first month is 875,21 $, the second one is 876.99 $, ..., and the last one in the last month is 1258,97 $.
View 5 Replies View RelatedI am trying to apply a multiplication factor that would vary depending on the sum of a particular cell. For example let's say the value of cell A5 is the SUM of A1:A4. Let's say the value is 255,000,000. my multiplication factor table looks like this:
1 - 50,000,000.........................(0.17)/1000
50,000,001 – 100,000,000..........(0.13)/1000
100,000,001 – 250,000,000.........(0.10)/1000
250,000,001 – 500,000,000.........(0.08)/1000
500,000,001 – 750,000,000.........(0.07)/1000
750,000,000 ++ .......................(0.05)/1000
So depending on the range the cell sum falls into I need the formula to apply the appropriate multiplication factor and divide by 1000. I'm trying to avoid manually inputting the (0.XX)/1000 based on the formula identifying which range the sum falls into and automatically applying the appropriate multiplication factor. In the example above the formula would be applied to the A6 cell and identify the A5 sum as falling into the 250,000,001 - 500,000,000 "range" and automatically apply the corresponding multiplication factor.
I would like to know the formula used in excel for rate function. Anybody knows and can share with me ? as i need to use that in other programmining language where these financial functions are not available.
View 9 Replies View RelatedIn Table A, I have the rate table for an insurance product. I need to build a formula that will outomatically populate the data in Table B based on plan, start age and deductibles.
Example, if a person selects age 32 years, deductibles as 0 and plan 4, then his yearly premium will be $750/12=$62.50.This will automatically increase when his age falls into the next age bracket which is 36-45 years and his premium at age 36 to 45 will be $970/12=$80.83.
The Table B should populate all this information from the start age to age 80 when the policy stops. An example of table B is shown in the attachement for someone who starts at age 32, chooses deductibles as 0 and plan 4.
I'm trying to calculate a sales run-rate which will change on a day-to-day basis, to predict the end-of-month sales total.
The invoice values are in the data range H17:H74 (I don't want the final formula to add up the refunds in this field i.e. negative values)
The date field is in data range C17:C74.
So basically the formula will need to add all invoice totals (excluding refunds) and divide by the current number of days worked in the month (not duplicating days in the date field) and then multiply this by the average number of working days in a month (21). This should should give a predicted end of month sales total.
Am I just making things up that are impossible to do on excel?
I've had a bet with the other guy in the sales office because I said excel can do a lot more than he thinks. He's under the impression that excel begins and ends with what they teach you at school.
Is there a function that calculates what interest rate is needed to turn a $2000 investment into $9000000 in 30 years based on compounding interest?
View 9 Replies View RelatedFromToGr Rate
90100A14.0
8689A23.6
8085B13.2
7279B23.0
6471B32.8
5663C12.6
This is part of a marks to grade and rating table. I have a list of students and the marks they obtained in an exam. I need a formula to put the grade and rate beside each name e.g. John; 92 marks; A1; 4.
Looking 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
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 RelatedI'm trying to set up a water bill calculator and am having troubles. We have a base rate that we charge for everything under 4000 gals. After that we have an increasing tiered rate. I'm trying to make a form so we can calculate bills accordingly. I don't know how to create an "IF" scenario.
example
If "total usage" is greater than 4001 but less that 8000 multiply by $4.50 per 1000 gal
If "total usage" is greater than 8001 but less that 12000 multiply by $4.75 per 1000 gal
If "total usage" is greater than 12001 but less that 16000 multiply by $5.25 per 1000 gal
If "total usage" is greater than 16001 but less that 20000 multiply by $5.50 per 1000 gal
If "total usage" is greater than 20001 and over multiply by $5.75 per 1000 gal
Is there an easy way to do this?
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.
As you can see in the attached sample file, I have different currencies, that all need to be converted to USD.
The exchange rate are displayed on sheet 2.
Currently the formula just picks all values without considering the exchange rate to USD. What I need to have, is a formula which, based on the currency, picks the correct exchange rate and outputs USD.
Apart from that, another thing the formula should consider is the value of G1 in sheet 2. With G1, the user can later define a specific type to be calculated by simply pressing down the button and chose either FG or RM.
The formula needs to:
Find out what currency the value is in and automatically convert it to USD via the exchange rate values. It also needs to consider the value of G1 in sheet 2 in order to filter out the other values. If, for example G1 is FG, then only FG should be taken into the process and therefore only values where type FG is marked.
find attached herewith a sample file.
View 4 Replies View RelatedI work with different currencies in my company, now I would to get an up to date state of the cask book. So I have $250, and 500EUR, now I want a formula (connecting to internet) that automatically multiplies the $250 with the current exchange rate, so I know how much I have in Euros in total.
View 5 Replies View RelatedI have a financial sensitivity sheet setup by year, where for example below each year is a cell you can change the exchange rate in and it effects the outcome of cash flow as these cells are linked to part of the finances. Then I have a "reset" button setup that is assigned to a macro. When you click the reset button all cells in the exchange rate row will be changed to a value that is entered in a "base case" cell. That way various years can be changed but also everything can be set back to the default or base case. My macro for that case is this:
View 5 Replies View RelatedI need a way to calculate commission for my sales reps. The commission is based off a calculated value in H39 of a sheet that I have developed.
They get 7.5% of the value when between $1 - $100,000, example: sales of $50,000 = $3,750 in commission.
If the value in H39 is > $100,000 but $250,000 but $500,000 they get 7.5% on the first $100,000 plus 5% on the amount between $100,001 and $250,000 plus 3% on the remainder between $250,001 and $500,000 plus 2% on any amount >$500,000 example: sales of $800,000 = $7,500 (7.5% on first $100,00) plus $7,500 (5% on the next $150,000) plus $7,500 (3% on the next $250,000) plus $6,000 (2% on the sales >$500,000 for a total of $28,500.
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'm doing an exercise for school and I'm totally confused here. I have to:
With the original assumptions, goal seek to determine what the Annual Rate Increase would be for the total expenses to be $175,000 (answer = 20.77%). Here's what I have: through the process?
I have a model to develop wherein i have to rate the performances on the basis of % results.
i.e.
A rating for more than 15%
B rating for results between 10% to 15%
C rating for results between 5% to 10%
D rating for results between 0% to 5%
E rating for results less than 0%
I know its simple though but real problem is different
I have 9 different measures for which i have 5 different ratings each which i need to apply over 8 different businesses.
I dont want to manually add these conditions & also want to make it dynamic for any changes in rating %.
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?
View 9 Replies View RelatedI 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 am trying to calculate the effective annual interest rate earned on an investment and find the results are close but not really accurate. I suspect because I have not included the frequency of interest in my existing formula
r = n * nt root (A/P-1)
where;
r = the effective interest rate
n = the number of times interest is added per year
t = the total number of years
A = the current value
P = the original value
The 2 problems I face are;
1. Confirming this formula would provide the correct answer (need maths expert here) &
2. How would "nt root" (as in sqr root, but using the product of the years and frequency) be used in Excel
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.
