Grid To Determine Interest Rates

Jul 23, 2008

I have a grid to determine interest rates. The are add ons to the rate that are dependent on two factors - the credit score and the Loan to Value percent or LTV, both of which are shown in terms of ranges (720-739, etc). See the screen shot below.

I have already written formulas for converting the score and LTV to the ranges as they appear on the sheet (ie, if you type in a score of 722, the formula converts it to the range of 720-739.

What I need to do is this - when the score and LTV are input and determine which set of add ons come into play, I need the sheet to take those add-ons, identified by an x next to it at the top of the page, and add them to the base interest rate, giving me the final rate. Again, see below to make this more clear.

I've thought of using VLookup, but I don' think that would be applicable here.

What's the best method, and can you give me an example of what a formula might look like?

Loan Level Pricing Adjustments

Base Rate 6.25 6.25

Credit Score 741 >740

LTV 60 740 Cash Out 0 0 0 0.25 0.375 0.375 n/a >740 Investment 1.5 1.5 1.5 2 2.5 2.5 n/a >740 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 >740 3-4 unit 1 1 1 1 n/a n/a n/a

720-739 all -0.25 0 0 0 0 0 0 720-739 Cash Out 0 0.125 0.125 0.375 0.5 0.5 n/a 720-739 Investment 1.5 1.5 1.5 2 2.25 2.5 n/a 720-739 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 720-739 3-4 unit 1 1 1 1 n/a n/a n/a

View 9 Replies


ADVERTISEMENT

Calculate Of Interest With Different Interest Rates Per?

Dec 29, 2013

I have a problem calculating a total interest for an input period that has different interest rates. You can see the attached file: INTEREST.xls

I have used a function but the result comes out wrong. Can I fine tune it or do I have to change my method of calculating.

View 10 Replies View Related

Daily Compound Interest Using Two Rates

May 16, 2014

Formula to calculate a daily compound interest based on the higher rate of the two rates for the first 5 years, then after 5 years the calculation would only be based solely on the blocked rate.

View 4 Replies View Related

Calculate Compound Interest Rates

Sep 22, 2011

I am trying to work out a formula to calculate compounded interest rates.

I have a table that is 24 columns wide (months).

1 row that will have amounts input in to the columns (these will be different amounts)

I want to have a row along the bottom that calulates the compounded interest at a fixed interest rate on the total amount that is in the first row.

I have managed to do this using a table but surely there is a formula for this as the table can become very troublesome if the input amounts change.

Ive added an image (attached) : excel.jpg‎

View 6 Replies View Related

Daily Compounding Interest Formula Based On Two Different Rates?

May 29, 2014

What I am trying to do is to create a formula for the attached spreadsheet - that calculates the daily compounding interest based on the higher rate of the two rates for the first five years then after 5 years the calculation should only be based solely on the blocked rate.

View 5 Replies View Related

Calculate All A Rates Seperate From B Rates And C Rates

Aug 15, 2007

formula is needed to get excel to calculate all A rates seperate from B rates and C rates example 10 A rates @ 50.00 9 B rates @ 40.00 and 6 C rates @ 30.00 so although the rates could be mixed up (not following in any particular order the result which I would like to appear on a separate spreadsheet would be A = 500.00 B = 405.00 C = 180.00 then to get them to total up = £1085.00

A 50.00
B 40.00 then separate sheet with answer a 100.00 b 40.00 c 60.00
A 50.00
C 30.00
C 30.00

View 9 Replies View Related

Performance And Potential Grid (nine Box Grid) - Multiple Values

Oct 24, 2013

I created a table where based in several competences anyone can evaluate performance and potential. My issue comes when based on that result (colum Y in "Perf&Pot" Sheet) I want to export that to a nine box matrix (Sheet 2 "grid" in the attached excel). As could be several people in the same box I wasn't able to com up with a formula that solves this.

Pasta1.xlsx

View 1 Replies View Related

Missing Rates

Jun 24, 2008

i have a bunch ofdaily rates back from 2005. sometimes ill have one or even 2 or 3 missing rates in a row. when there is a blank rate, i just want excel to calculate the average of the date below and the date after. right now, ive just been going manually to each missing date's rate and calculating the average.

View 9 Replies View Related

Updating Hourly Rates

Mar 27, 2007

I am trying to create a conditional formula. I have multiple workers that work for different $$ per hour. I am keeping the hourly rates on one page for security purposes (the person that is going to be updating the hours worked is not very computer savy and they less they have to type the better) and the hours on another... I want copy a formula down the page that will calculate the hours to the hourly rate. The issue is that i want the formula to look at all of the hourly rates (assume they get raises) for Joe and multiply the most recent hourly rate times the hours... in addition I don't want the formula to re-calculate when a new hourly rate is put into the hours sheet.

Example

Jan. 1 joe works 8hours at $10 per hour formula in A1 (or wherever) calculates $80
Jan. 2 joe gets $1 raise
Jan. 2 Joe works 8 hours at $11 per hour formual in A2 calculates $88 but formula in A1 maintains the $80 and does not calculate the $1 raise.

View 12 Replies View Related

Excel Add-in For A Series Of Rates?

Oct 25, 2011

There is a database of rates, stored in an excel table. There are dates (in the format MMYY) and corresponding currencies (100 in total)

I would like to have a custom function where I could type

=RATELOOKUP("EUR","0911")

And the corresponding rate will appear

The excel file with the rates is stored read only in a locked folder on the network so the format and layout will not be affected

How could I macro this? - then turn the result into an addin so that everybody can take advantage of this.

View 9 Replies View Related

Calculating Success Rates

Jul 20, 2006

I have three columns. The first column contains a certain category ( i.e. Cat 1, Cat 2, ect.), and the second and third columns contain numbers referring to that category:

Ex:
A1 B1 C1
Cat 1 6 5

I want to be able to find the success rate (C1/B1), but I want Excel to recognize what category it belongs too so I can split the success rates into categories in a different location. What function, if there is one, do I use, and what information do I need to plug in.

View 8 Replies View Related

Live Currency Rates

Dec 7, 2006

I know that Excel has a function to import currnecy rates from the MSN Money web site. But MSN Money doesn't support all currencies. I would like to build an excel sheet where a user can maintain the currency codes and the currency rate is being fetched from the internet. Does anybody have a suggestion from where I could get currency rates into Excel?

View 9 Replies View Related

Calculating Using Times And Different Rates For Bookings?

Oct 14, 2013

I am trying to create a booking calculation sheet for facilities. I want it to create something like a quote for customers. So when booking facilities, the hourly rate changes after 6pm. What I want to do is to put in a start time and a finish time and it calculate how many hours are before 6pm and charge them at $12 per hour, as well as how many hours after 6pm and charge them $18 per hour. I have tried a few things but they don't seem to work. I'm struggling with the logic of it really. This is mainly because the start time may or may not be before 6pm, as too the finish time.

View 6 Replies View Related

Calculation Time And Charge Rates

Oct 8, 2007

I am trying to figure out a formula to figure out how much to charge for hrs of a rental. There is an automatic $1000 charge regardless of time used. the rate chart is as follows:

$1000 + hourly charge = total

=<50 hrs= $15/hr
>50hrs but <100hrs= $6.50/hr
>100hrs=$4.10/hr

ex: so is A1= 200hrs then i want B1 to equal $1820.($1000 + (200 x 4.1))=1820.

I have an idea on how to write the formula, but i am having a little bit of trouble with it.

View 14 Replies View Related

Calculating Monthly Growth Rates

Feb 28, 2008

I have US money supply data, arranged monthly from 1975-2008. I need to calculate the monthly growth rates. I would really appreciate some help as I have no clue how to do this.

here is a link to a text version of the data I am using: [url]

View 9 Replies View Related

Calculating Charges Based On Stepped Rates?

Jun 20, 2014

Never tried complicated formulas in Access and at a bit of a loss... What I am trying to do is calculate a utility bill based on stepped rated.

For example:

Usage up to the first 500KHW is billed at .067 per KWH
Usage after the first 500KWH from 501 to 999 is billed at .044 per KWH
Usage from 1000 up is billed at .0318

So if my usage was 1200 KWH...

((500 x .067)+(500 x .044)+(200 x .0318)) = 61.86

I was assuming it would require an complex "if" function to split the 1200 into steps and then calculate charges per step?

View 9 Replies View Related

Create VBA To Distribute Rates And Exclude Weekend

Jun 24, 2014

I have to create VBA according to these criterias. I have a list of 22 rates that i need to distribute by order for a month period (30 days) noting that I need to skip weekends (Friday and Saturday)

Day 123.06.2014Monday20%
Day 224.06.2014Tuesday10%
Day 325.06.2014Wednesday7%

[Code]....

create this formula so that each time I change the date the rates are distributes accordingly

View 4 Replies View Related

Time Formula - Calculate Labour Rates

Aug 23, 2012

I want to calculate labour rates as follows:

06:00 to 18:00 std rate $10
18:00 to 06:00 night shift rate $15

I want to be able to type in the hours worked e.g. 11:00 to 19:00 and would expect 7hrs at $10 and 1 hr at $15.

View 1 Replies View Related

Formula To Validate Invoiced Rates Against A Rate Card?

Mar 23, 2014

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

View 8 Replies View Related

Employee Hours Report With Multiple Rates And Categories

Sep 25, 2013

I am trying to create a a file that will serve as time log and dashboard report for consultants who work a maximum of two hours a day, with a strict(fixed) calendar schedule with multiple sessions. Some consultants may work one hour of a particular schedule on category A(Lead) and the second hour as category 2. How to create a sheet that sums the rates for all the categories worked on a given day without using a macro. Here is my sample data

Pay Categories:
Lead
Assistant
Admin

Pay Rates
Lead-$75/hr
Assistant - $40/hr
Admin - $20/hr

Sessions and Session Dates sample:
Session 1 - 10/8/2013 to 11/8/2013
Session 2 - 11/15/2013 to 12/15/2013
Session 3 - 01/06/2014 to 03/15/2014

*Note the dates will or should be listed in separate columns in order for them to be used for daily logging.

Employees (Sample)
Jane Doe
Jack Doe
John Doe

Desire goals of the report:
1. Daily log as employees work e.g if Jane Doe works on 10/8/2013 as a Lead and Assistant, ideally the data entry person should be able to enter these two values on one cell or pick the relevant value from a set list
2. The report would then match the value of the data in the cell for Jane Doe on 10/8/2013 with a table array with categories in one column and the pay rate in another, and return the appropriate pay value which can either be a sum of the two rates or the total hard coded into the cell.
3.Be able to create a dashboard report that would sum the totals by employee, month,sessions etc in a separate sheet vs other criteria such a budget etc.

View 1 Replies View Related

Aligning Two Series Of Currency Exchange Rates With Different Dates?

Nov 28, 2013

I have two different columns of currency time series data with dates that do not align. One trades Monday-Friday, the other seven days a week.

I want to either a) add dates to the shorter series that only trades Monday - Friday (and just use the closing price on Friday for the Saturday and Sunday dates)

b) (not as good an option, as I will lose data) is delete the Saturday and Sunday data from the longer series that trades on the weekend.

View 1 Replies View Related

Macro To Access A Website And Copy Exchange Rates

Jan 25, 2007

My task is to somehow access [url] and copy the exchange rates on the table to my Excel workbook. Obviously a macro will not record outside of the Excel program, so I am just wondering if this is possible at all (without hacking of course). My plan B is to have the user manually enter the exchange rates.

View 3 Replies View Related

Drawing Grid

Nov 26, 2007

I want to have 2 cells where I can enter 2 numbers (length & height) so that a grid can be plotted. For example, a big sheet of paper size is length 30" x height 8", if first number is 10" (length) is entered, it will plot 3 lines across 30" (cos 30" divided by 10" equals to 3) and if I enter 2" (height) on 8", it will then cut it into 4". Attached a file for better understanding.

View 14 Replies View Related

Grid To List

Jun 15, 2009

I'm not worried about the order of the data. What I want is information on turning a grid into a list.

View 4 Replies View Related

Lookup Value From X - Y Grid?

Jan 16, 2013

I'm creating a fairly large risk analysis spreadsheet which uses a fairly standard 5x5 risk matrix.

I need a function or a formula which refers to the score a cell in a 5x5 grid, if given the row and column values, like this (where the columns are probability and the rows are impact):

VL
L
M
H
VH

VL
1
2
4
6
7

[Code]....

View 3 Replies View Related

Grid Header From Cell Value

Jun 29, 2013

Example.xls

Within my attachment, I have a grid of data, Row A are headers, and beneath, various corresponding dates, there are no row headers.

I'm trying to do a lookup so that column header is returned bases on a exact cell value.

Example: inputting 03/03/2014 will return "Week 3"

Week 1|
Week 2|
Week 3|
Week 4

07/07/2013
|14/07/2013
|21/07/2013|
28/07/2013

[Code] ......

View 5 Replies View Related

Linking Grid To Combobox?

Mar 6, 2014

I'm trying to link objects and pricing to groups via a combo box. If the user selects Group A from the combo box and enters Object A on the line item, I want the price associated with Object A and Group A to populate.

Group A
Group B
Group C
Group D

Object A
$1
$3
$5
$7

Object B
$2
$4
$6
$8

Object C
$3
$5
$7
$9

Object D
$4
$6
$8
$10

View 9 Replies View Related

Marking Grid Points

Nov 18, 2008

I have a worksheet ('Matrix') that has a grid that is 40 rows x 40 columns. On another sheet ('Drivers') I have a two columned table where a user is enters two numbers between (you guessed it!) 1 and 40. What I'm trying to do is within the grid, have a "Y" appear on the crossing point as denoted by the table. I've tried a couple of experiments with MATCH, but I'm not getting very far.

I've attached an example document to make this a bit clearer and I've coloured the references on the 'Drivers' sheet to correspond to where they should appear on the 'Matrix' sheet. Sorry if that's a bit garbled. Let me know if you need any clarification.

View 4 Replies View Related

List Box To Use As Data Grid

Aug 13, 2008

about using list as a datagrid type. I was able to research some samples and applied it on my own, but unfortunately I am missing something again here. The data on the worksheet are not being populated when my range is on the AA column. It works with A1 column but not the far ranges. Im not sure about the limit of range anyway here is the

View 14 Replies View Related

VLOOKUP & Grid Names...

Oct 15, 2008

I'm having an issue with VLOOKUP, I'll keep it short as I don't think I need loads of details because I have done some troubleshooting and believe got to the cause of the problem... I just don't know how to fix it!

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved