How To Find Conditional Interest Rate

Sep 15, 2013

find attached herewith a sample file.

View 4 Replies


Interest Rate Function

Mar 6, 2007

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 Related

Calculate Daily Interest Rate

Nov 8, 2008

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:

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.

View 2 Replies View Related

Calculating Annual Interest Rate

Aug 18, 2009

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 Related

Compound Interest Rate Formula

Jan 27, 2007

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)

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

View 9 Replies View Related

How To Calculate Interest Rate For Loan Product

Jan 31, 2014

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

View 2 Replies View Related

VBA Code For NPV Function With Variable Interest Rate?

Feb 2, 2014

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?

View 8 Replies View Related

Calculating YIELD From Interest Rate And Tenure

Jul 6, 2013

I keep coming across bonds having different annual interest rates and different compounding frequencies (quarterly, half yearly and yearly).

I know there is a YIELD function, but it requires so many inputs. I was wondering whether we can calculate cumulative yields just from annual interest rates, compounding frequency and investment duration?

View 1 Replies View Related

Interest Rate Calculation With Payment Increases

Mar 4, 2014

I have a capital lease amortization schedule with annual increases to monthly rent that I am trying to solve for an interest rate such that the balance nets to zero at the end of the term. I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month. The present value is known, payment term is known, future value is zero, and the payment amounts increase annually. These assumptions may change for new leases so ideally the solution would be dynamic, adjusting for shorter/longer terms, etc. Here is an example of my assumptions:

Payment start date: 8/1/13
Term end date: 10/31/2025
Rent length in months: 144
1st months rent: $500,000 - payments are due at the beginning of the month and are paid monthly
Annual rent escalation: 3% - i.e. 1st 12 months is $500k/month, 2nd 12 months at $515K, etc.
Beginning NPV: $75M
Ending value: $0
Imputed annual interest rate: UNKNOWN

I'm not sure if this is relevant, but the monthly payment is allocated between principal and interest. Monthly interest expense is calculated as the current balance * (imputed interest rate / 12).

Currently, I've plugged the interest rate such that my ending balance is 0, however I was hoping to calculate it on the fly as opposed to manually plugging it.

how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?

View 7 Replies View Related

Effective Rate Calculation, Interest Only Loan

Aug 27, 2008

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%

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.

View 9 Replies View Related

Finding Interest Rate Using Loan Constant - Iterative Calculation

Apr 9, 2014

I have a created a Data Chart Below. A - C are the columns and 1-7 are the rows. I have hard-coded the equation in cell B5 that I am using.

What I want to do is input an number into B3 that automatically makes Cell B5 equal to Cell C1. Is there a process in excel you can use to do this. Or do you just have to use trial and error?

1Loan Amount$10,500,000 0.0730041581143804
2Term 10 years
4Amortization30 years
5Constant (K) =PMT(B3/12,(B4*12),-1000)*0.012
6Annual Payment$766,544
7Monthly Payment$63,879

Example is attached.

View 1 Replies View Related

Calculate 10% Or 20% On Investment Form Depending On Interest Rate In Column

May 25, 2009

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

View 5 Replies View Related

Conditional Divide (probs Occur When I Manually Enters The Exchange Rate)

Sep 25, 2007

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.

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

View 7 Replies View Related

Multiple Date Ranges To Find Effective Rate?

Jun 18, 2014

My problem is trying to identify the applicable rate in a range of dates that are not consistent in every case. I have a number of orders that span 4 years. The rate charged has changed over time and therefore I'm trying to find what the applicable rate would be for that time frame. For example one of my orders was created on 2/27/2012 with a specific item, then again the same item was ordered on 9/10/2013 and I need to find what the rate should have been for both of those orders during those rate periods. I've attached a sample sheet with the 2 tables I've been trying to assess. I've tried using mulitple IF and VLOOKUP formula's but it doesn't work they way I need it to due to the inconsistency in Table 2. H

View 2 Replies View Related

Find The Commission Rate Per Worker Using Lookup Functions

Dec 24, 2005

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

View 12 Replies View Related

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

Compound Rate: Annual Growth Rate %

Jun 4, 2007

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%

View 3 Replies View Related

How To Calculate Trend Line Growth Rate (as Annual Percentage Growth Rate)

Feb 13, 2014

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 Related

Lookup Or If Function: Find The Room Nights And Net Room Revenue Values For The Specific Rate Code In The "MATRIX" Workbook

Apr 7, 2009

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.

View 2 Replies View Related

Interest Functions

Dec 3, 2005

The file I am working with is available here

(1) I need to work out the value of 2 years worth of interest on a loan - details would be

Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months

I am currently using an ammortisation speadsheet which calculates the interest per month etc ... at this time I simply highlight and calculate the interest which would be two years worth ...

the problem is this calculation varies from the bank interest calculations (although the repayments are the same and the total interest payable amounts are the same)

The banks calculations are said to be done on the basis that the minimum monthly repayment is met and there are no additional fees thrown in ... so I cant understand the difference ...

(2) In addition to the above question, but working on the same loan, traditionally amortisation tables calculate the amount of the repayments, the interest component and the principal component ... some include a column where you can factor in additional repayments on a row by row basis (like the one I am referring to) the table assumes that the payment is made on the due date ...

what I would like to be able to do is
(a) insert the actual payment dates
(b) if no payment is recorded for that period, automatically insert an overdue fee

View 14 Replies View Related

Interest Over 'X' Months

Mar 20, 2008

I'm trying to find a formula that will give me the following:

Rent is $2.00 a month with a 3% escalation each year. What is my Rent after 5 years?

Now the other thing is that I need the number of years to be a formula that counts the number of columns with data up to the column with $0.00.

View 9 Replies View Related

Interest Calculation

Apr 28, 2008

I have a macro that formats a spreadsheet to show outstanding invoices, grouped and subtotalled by month. To add to this I need VBA code that will use the subtotals to calculate interest on overdue accounts.

Interest becomes due a calender month after the month in which the invoice is dated. So for example a January invoice would start to accrue interest on 1st March.

Below is the subtotals code (sadly the totals don't adjust if data is added or removed but perhaps that is another question for another day.)

Dim LastRow As Long
Dim NextMonth As String
Dim R As Long
Dim Rng As Range
Dim SubAmount As Currency
Dim ThisMonth As String
Dim TotalAmount As Currency
Dim Wks As Worksheet

Set Wks = Worksheets("Reconciliation")
LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Wks.Range(Cells(2, "A"), Cells(LastRow, "D"))

View 9 Replies View Related

Compounding Interest

May 6, 2008

attached the spreadsheet that I need help with in that same thread. Please check out the spreadsheet via: [url]

Here's my issue:

I'm trying to be debt free roughly around 10-11 years years based on my current plan. SO, what I am trying to do is figure out what I can do with my savings at 1, 2, and a 3 % rate of return in a savings account that is compounded monthly after that debt free point. My time frame for results are 5-10-15-and 20 years.

View 9 Replies View Related

Conditional Format And Or & FIND

Jul 12, 2009

In column B I could have blank, text or text containing the word "track" or "Track". In column C I could have Blank, "yes" or "Yes" or "no" or "No"

Only if B text contains "track" or "Track" and/or C contains "yes" or "Yes" I want to conditionally format B.

This does not work:-


View 5 Replies View Related

Cumulative Interest Formula

Nov 18, 2008

I have a person who was paid £1000 pension pa for ten years. I've found out that the pension should have been split 50/50 with half increasing by 5% pa. Thus in year 1 total pension would be £1000. In year 2 the total pension would be £1025 ((£500 x 1.05) plus £500)

I know that in year 10 the total pension should be £500 (non increasing) plus £814 (£500 x 1.05 to the power of ten). My problem is how do I work out a formula which calculates the total arrears due in year ten? I'm thinking the arrears due after the ten years is £314 but something is telling me it's a lot more.

View 3 Replies View Related

Interest Template: How To Sum Up According To Date

Sep 9, 2009

I have created a template that I use to determine interest calculations (it is based on simple daily interest, but monthly compounding and adjusts for any variations in interest rates during the month), and it seems to work fine so far.

However, I am trying to make it better and would like to eliminate as much manual input as possible. I have attached an example of the template to help explain what I would like to achieve

The Running Balance is dependant on column "J" - Interest for the Month. At the moment, I am manually adding up interest accumulated for the month, however if I add one cell too high or miss a cell when adding, it can lead to compounded problems further down. So in short I am looking for a method that would automatically calculate "interest for the month" by adding all the amounts in column "I" (Interest Daily) at month end for that month only (dates are entered in Column B)

In the attached template, grey areas indicate capital movements and/or interest rate changes that may occur during a month (ie movement in the month in question that affects the daily interest calculated)

I hope I've covered everything!

Any help in this regard comes most appreciated. If anything is unclear, I will do my best to explain what I am trying to achieve in greater detail.

View 7 Replies View Related

Calculating Compound Interest

Aug 6, 2006

I have a column of years and a column of numbers representing annual
amounts placed in a savings account for the year. I would like to calculate
the balance of interest earned added to the balance of the account and then
calculate the interest earned on the accumulating amounts each year. The
results would be the account balance displayed in an adjoining column. So
far, I have not found a worksheet function for that. Could someone point me
in the right direction? Perhaps there should be several columns of data?

View 10 Replies View Related

How To Sum Interest During Calendar Period

Oct 13, 2011

How can I find the total of interest (or and expense) during a given calendar range

Data is as follows

Date Type Amount
4/1/11 Interest 4.00
4/15/11 charge 22.00
5/1/11 interest 3.00

I want a cell to compute the interest in april only.

I have tried =sumif(range, end date, amount_range)- sumif(range, beg date, amount_range)

but how do I nest the type argument into the argument

Possible approaches advanced filter? and function? add another column that uses an if statement to do interest only and do the sumif statement above I was hoping to have a drop down box that could have interest, COGS, beside it and april, may, june above it

View 1 Replies View Related

Compounding Interest Formula

Dec 10, 2007

I am trying to set up a budget for my self. Of course I am trying to make it as complicated as possible.

I have done searches online and that is how I found this forum. I am finding amortization formulas, but not what I want

Here is my question.

I have two fields, Mortgage and auto loan

I want to have a field that does a calculation for me.
I know following fields as an example making numbers up
Interest rate is fixed at 5.5%
Amount left on loan is 150,000
Loan is fixed at 30 years with 28 years left

I want to run a formula so when I make a monthly payment of 1000 dollars how much of that goes to interest, how much to principle (not to make this complicated even more, but let say that I have 200 escrowed which I am not sure if that has interest calculated on(I donít think so as it is property tax?))

Same example for car loan.
5 year loan, 4.5% interest, payment 438 a month.

View 9 Replies View Related

Simple Daily Interest

Aug 31, 2008

does anyone know how to calculate the interest so it matches this? .......

View 13 Replies View Related

Copyrights 2005-15, All rights reserved