# RATE FORMULA

Aug 23, 2007I 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.

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.

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%

FromToGr 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 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 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 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 would like to a formula that will provide a weighted average rate (yellow cells) for a provided volume level, given the tiers on the left. I have already performed this the "dumb" way with lots of IF statements to figure the volume by tier and then taking a weighted average of the products with the rates by tier, but there's got to be a more efficient way. My file is huge! ....

View 9 Replies View RelatedI have a spreadsheet, but I need the following formula to make our lives easier here.

I have the following columns:

Rate, I/T/P/S, FT/PT

I need a formula that when I enter "I" for Infant it will give me the FT (full time) or PT (part time) rate. All the columns have drop down menus, so if I choose "I" under the ITPS column and then choose FT, I want the formula to be able to give me the full time rate which is 157.94

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 need to know if its possible, and if it is, what the formula would be to get the correct "Rate" to pull based on the criteria given:

User would input the following information:

Zip Code: 56559

Pallets: 3

Weight: 1200

The formula needs to use the following table to use the criteria listed above, to fine the correct "Rate". The "Zip Code" and "Weight" both need to fall between the correct ranges and then match the "Pallet" to find the correct "Rate".

From Zip Code

To Zip Code

Pallets

Weight From

Weight To

[Code]......

We have agreed maximum rates with suppliers for certain services and I'd like to check that the rates they have invoiced fall below the agreed maximum.

I've attached an example of the data I'm working with. What I'm after is a formula that will cross check the details and rate charged on the 'Invoiced' tab against the three 'rate card' tabs and generate an output that flags any discrepancies. I've added a column called 'Validate' on the 'Invoiced' tab where I'd like this formula to go.

All the data should match with the exception of the shift which is listed as a description on the rate card but is a concatenation of the job role and an abbreviation of the shift (D = Days, N = Nights/ Saturday and O = Sunday/ Bank Hols).

Column A = persons name

Column B = number of hours

Column C = trade

Column D = shift (as in day, night or weekend)

Column E = I would like the formula that looks at the trade and time and then puts the correct rate.

I have a little table on the site with trades/times and rates. IF and IF THEN do something

I took a screenshot to show: [URL] ..........

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

=CELL("address",Index()), but it won't work.

I am trying to create a formula that will allow me to enter a zip code and have excel return the specific tax rate for that zip code. I have zip codes in excel for all of California along with the corresponding tax rates. I am creating a form that I can just input the zip code and have the tax rate automatically pop in.

View 1 Replies View RelatedI am looking to setup a formula to calculate a Fee that is based on the number of users of a service in a month, with the rate varying by the number of users. The first million users in a month will bring in $0.40 per user, for users 1-4M it will bring in $0.30 per user, for users 4M-7M it will bring in .20 per user, and for users beyond 7 million it will be .10 per user. So for example, if a user count was 9 million in a month, the calculation would be ($0.40 x 1 million) + ($0.30 x 3 million) + ($0.20 x 3 million) + ($0.10 x 2 million). I know the answer to the problem is obviously $2,100,000 but I can't build the formula that solves that and can handle instances where the user count is capped in one of the individual brackets. (IE if there are 3.5M users)

I've attached an example spreadsheet : Variable Fee Schedule.xlsxâ€Ž

I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.

writing a formula to evaluate over 360 periods.

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?

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.

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.

In 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 RelatedI 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'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 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'm trying to determine the percentage rate of change in a trendline. I've got a sales chart with 90 days worth of sales figures. I can see that the trendline is trending down, but I'm not sure exactly how much.

If you take the first data point in a trendline and subtract it from the last data point and then divide that by the first it should give you the percent change. Excel must be able to do that math for you but I can't even figure it out to save my life. I can't even figure out how to display the first and last data point much less the "rate" of change I'm looking for.

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

I would like a function which works like the NPV function except that the interest rate should be variable and a range rather than a single number.

Is it possible to see the code for the excel NPV function so that I can modify it?

Or alternatively has seen such a function?

ADVERTISEMENT