Tracker
HOME    TRACKER    Excel

# RATE FORMULA

## 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.

Related Forum Messages:
Compound Rate: Annual Growth Rate %
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%

Flow Rate Formula
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

Formula To Put The Grade And Rate Beside Each Name
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.

Compound Interest Rate Formula
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

Formula That Will Provide A Weighted Average Rate
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! ....

Formula To Be Able To Give The Full Time Rate
I 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

Last Purchased Rate
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.

Look Up A Rate Based On Another Value
I 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.

Rate Function
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?

Interest Rate Function
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?

Populate Rate Table
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.

Calculate Subscription Rate
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?

Change SUMPRODUCT To IF With Exchange Rate
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.

Calculating Annual Interest Rate
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?

To Rate The Performances On The Basis Of % Results
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 %.

Sales Run-rate. Too Tough For Me
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.

Range Value: Change The Exchange Rate
I 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:

Calculate Daily Interest Rate
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.

Stacked Column Chart With Rate Line
Here's a sample of a chart that I want; However, I want the bar chart to be a stack chart as I have multiple "Failure modes" per month that I want to show the number(or count) of failures per month. Then I want the line chart to remain the Complaint Rate for that month.

I just can't seem to figure it out.

This chart pulls from a data set that looks something like this: ....

Determine The Percentage Rate Of Change In A Trendline
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.

Effective Rate Calculation, Interest Only Loan
I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas?

Here is the effective rate calcs on a random normal amortization loan:

this is in B2, and answer is 7%
=RATE(B4*B5,-((B3+B7)/B6),B7)*12

B3 = Total*Interest 279017.8
B4 = #*Years*in*Loan 30
B5 = #*Payments*/*Year 12
B6 = Total*Payments 360
B7 = Beginning*Principal 200000
B8 - Ending*Balance 0
problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan.

Here is the example I'm working on... A client's loan is the following:
Loan amount - 131,538
interest rate - 6.15
30 year amortization
10 years interest only

normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about \$178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan.

Different Royalty Rate Depending In Unit Sales
I need to calculate a royalty rate due which is based upon a Unit Price * Unit Sales. The royalty rate due changes at certain levels of sales.
I've attached sheet to hopefully make clear.

Calculate APR (Annual Percentage Rate)
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.

Function To Work Out Monthly Compound Rate
Im trying to work out the formulae or fuction that will work out the monthly compound rate of a loan.

The loan details are £140,000 at 7.55% APR for 20 years.

Extract Out Of A Column The Greatest Rate Of Change
I have wave data and need to extract out of a column the greatest rate of change i.e. start from a1 if the numbers are going down find the first number that starts going upward...and put in column b...then find the next first number that is going down and put in column b....and so on and so on all the way down the column.

Increasing/decreasing; Rate Of Decrease - Spreadsheet
I’m trying to make this:

Amount of money: 1000 (changeable)
Beginning # of units: 0,1 (changeable)
Delta: 200 (changeable)
Rate of decrease: 50% (changeable)

0,1 unit should be added for every 200\$ increase (1000+200=1200; 1200+200=1400; 1400+500=1900; 1900+256=2156 etc)
0,1 unit should be taken away after decreasing by100\$ (because the rate of decrease = 50% (changeable))

After increasing of 1000 + 200, quantity of units should also increase for 0,1, so it should be 0,2 (1200), 0,3(1400), 0,4(1600)….. Amount of money can increase not only by 200, but for any sum. If that sum is (for example) 500, we should increase by 0,2 etc.

But when this some decreasing we are using rate of decrease (for example 50%, (changeable):
0,4 units – 1600
0,3 units – 1500
0,2 units – 1400
0,1 unit – 1300

Rate Numbers Based On Numeric Range
In the attached excel file in column C, beginning on row 4, are numbers. These numbers will automatically update on my bloomberg terminal.

(1) I would like to then in column E to have a rating given to those numbers based on the below scale. The way the scale works is that if its 0.6 its a 10. If its anything slightly above 0.6 exactly, its a 9. A 10 is also a 0.5 and any number in between 0.5 and 0.6. The scale is below.

(2) Given that the formula that is being updated is a bloomberg excel formula (in the attached document I pasted it as values), will an excel formula be able to read the updated number in the bloomberg formula? If not, in column D is there a way to have it automatically paste D as values (and update each time column C changes or workbook is saved), and then have column E read off of those numbers.
Scale:

10 = 0.5 to 0.6
9 = 0.6 to 0.7
8 = 0.7 to 0.8
7= 0.8 to 0.9
6 = 0.9 to 1.0
5= 1.0 to 1.1
4= 1.1 to 1.2
3= 1.2 to 1.3
2= 1.3 to 1.4
1= 1.4 to 1.5
0 = anything greater than 1.5

Calculate Earnings From Time Worked X Hourly Rate
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?

Median Hourly & Annual Rate In Same Column
How can I get the median of a column that contains both hourly and annual rates? I would like to either multiply the hourly rate * 2080 or divide the annual rate by 2080 then get the median.

Find The Commission Rate Per Worker Using Lookup Functions
I have a sheet listing comission rate eg. sales less than \$200, the rate is
..5%, less than 300, the rate is 1%..etc.

Then i have another table showing different sales value of different workers. How do i find the commission rate per worker using lookup functions??

Calculate Phone Call Costs Based On Rate Per Second
I 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.

Re-Format/Layout Rate Table On Based Another Sheet
I have a database in this format:

IDProgram NameNIDCvg BeginPlan CodeRate
B00001RT1WILLIAM9999999991/1/03INDEMA5
B00002RT2RAMON9999999997/1/06INDEMA2
B00003RT1MARY99999999910/1/04INDEMA5

The number of rows in the database changes from month to month. I'm trying to write a macro that will pull rates off of a rate table on another sheet in this format:

Plan12345
CCAREA RT1\$0.00 \$0.00 \$0.00 \$0.00 \$0.00
CCAREA RT2\$0.00 \$0.00 \$0.00 \$0.00 \$0.00
INDEMA RT1\$225.81 \$436.89 \$415.78 \$595.20 \$320.80
INDEMA RT2\$146.36 \$277.76 \$264.62 \$376.31 \$205.49
PACIFA RT1\$193.07 \$434.40 \$415.08 \$598.49 \$337.85
SH RT2\$74.08 \$148.16
DELTA RT1\$25.85 \$51.70 \$82.74
DELTAP RT1\$39.26 \$78.52 \$126.76
RLIFE RT1\$1.25

I'm trying to create this in a way that the user won't have to manually fill in the formulas each month when the size of the database changes. I can also reformat the rate table if need be, but not the database. I've attached a sample file so you can see what I'm talking about. I'm fairly new to VBA.

USD Equivalent In Column Based On The Exchange Rate At The Time
Dates in Column A
Currency in Column B (expressed as EUR, CHF or GBP)
Amount in Column C

And I'm trying to get the USD equivalent in column D based on the exchange rate at the time.

In Sheet 2 I have a table of the historical exchange rates like this:

DateEURGBPCHF
08/05/20060.786270.538151.2277
09/05/20060.784470.536751.2224
10/05/20060.781360.536271.2185
11/05/20060.778050.530761.2108
12/05/20060.775880.528811.2019
15/05/20060.779780.530911.2089

Calculate 10% Or 20% On Investment Form Depending On Interest Rate In Column
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%.

IRR- XIRR - Annualized Rate Of Return On Single Transaction
Here's my problem:
A) This is regarding a real estate transaction.

B) Assume the funds to acquire the investment total \$60,000.

C) Assume the purchase date is 1/1/10.

D) Assume the sales price (including costs) would total \$76,000.

E) Assume the sales closing date is 4/1/10.

F) What formula function would allow me to show the Annualized Rate of Return on this transaction?

I Know this is something I should have been able to figure out -- but when I use the XIRR function, I end up with a figure of 167% -- and common sense indicates that's just not correct....?

What I need to do is just find a way to show either the IRR or the Annualized Rate of Return for investment consideration purposes. Is it possible to do this based on one single transaction that occurs in a fraction of a year?

If Then AND Else: Figure Out The Vacation Accrual Rate Based On Length Of Employment
I would like to know how to figure out the vacation accrual rate based on length of employment.

Lets say for years 0-5 the accrual rate is 3 hours per pay period.
for years 6-10 the rate is 5 hours per pay period
for years 11-15 the rate is 7 hours per pay period
for years 16-20 the rate is 9 hours per pay period
and for years after20 the rate is 10 hours per pay period.

I want a formula that will look at say cell C5 (which is the cell that includes length of service) and output the accrual rate. Example. The number 12 is in cell C5. I would like the cell with the formula (D5) to say the number 7 (which is how many hours accrued per pay period based on the table above.

Calculate The Weighted Average Of The Win Rate Based On Volume Of Calls
I have 3 sets of data for two different groups:

Group 1 - Inbound
- Total volume
- Win rate (gross adds/total volume)

Group 2 - Outbound
- Total volume
- Win rate (gross adds/total volume)

I need to calculate the weighted average of the win rate based on volume of calls. Is there any way to do that?

Conditional Divide (probs Occur When I Manually Enters The Exchange Rate)
The file is a simple sheet which upon entering the actual/Invoice cost (C5), calculates the estimated landing/final cost (C8).

In between the process involves changing the currency from US\$ to PKR, make some calculations, and changing back the currency again to US\$.

The default rate of currency exchange is set to ave 60 (C12). However if the user knows the current rate he can put it manually in C6 and sheet will make all the calcs on this instead of using the default rate.

Problem:
Everything is working just too perfect. But probs occur when i manually enters the exchange rate.

It does successfully changes the US\$ to PKR and calculates everything perfectly but doesnt reverts the final cost back to US\$ successfully instead it keeps using the default value instead of user's value

Calculate The Future Cost Of Education Fee Assuming A Given Inflation Rate
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.

Calculate Balance To Zero Based On Constant Fix Rate & Withdrawals
I 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..............

Calculate Employee Vacation Accrual Rate & Number Of Days Taken/Left
Our office has a vacation days accrual policy based on number of years worked. We have a vacation days number, based on year of employment, the employee earns monthly. I need help with a worksheet, formulas, to document each employee, the year of employment they're in, # of days they have available based on the current month (which would need to add up automatically as the year progresses), then any days they request off, and finally a remaining balance of days left.

Lookup Or If Function: Find The Room Nights And Net Room Revenue Values For The Specific Rate Code In The "MATRIX" Workbook
I am having a bit of a problem creating a formula for this report that I have to update every week. I need to be able to find the room nights and net room revenue values for the specific rate code in the "MATRIX" workbook.

On "CURRENT REPORT," I need D4 to pull up the value on "MATRIX" that equals "Room Nights" in column H and "CONABC" in column J. The same thing needs to happen for F4 but with "Net Room Revenue" in column H. The full report has about 500 of these codes in column J, and I need a formula that I can copy easily and will not be affected if codes are added or removed. This is super last minute - I need to finish this report by tomorrow morning, so take a look at the attachments.

Formula For Deleting A Formula When The Formula Has Done Its Calculation
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.

I've got a formula

=IF(COUNTA(A1)=1,TODAY(),"")

When something is typed in Cell A1 the cell with the formula will input todays date.

Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.

Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take
=A1+B1+C1:

and produce:
=Bananas+Apples+Oranges

Vlookup Formula In VBA - Object Error (input A Formula Into A Cell)
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.

I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B\$1:C\$2000,2,FALSE)&J6

Combine Formula Which Reduce The No. Of Additoanl Formula's Columns
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.

To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.

Is There any formula to show fail subject in Remarks column without creating 5 additional columns?

This task joins a string together based on a number of characters per cell in the range.

I want to isolate one range, Col N, and add an IF condition to it.

There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....

Date Formula & Add If Statement To The Existing Formula
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?

Not Responding: Added A Formula To A Spreadsheet With Som Complicated Formula
I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.

Replace Character Used In Formula Where Formula References Cell
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.

i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6

this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads

I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire