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.
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 am trying to set up my excel to calculate daily compound interest.
The amount is 10,000 at 0.75% per day for 6 months.
I have tried several different things with no success -
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 RelatedIs 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 Relatedfind attached herewith a sample file.
View 4 Replies View RelatedIf 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 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 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?
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?
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?
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.
does anyone know how to calculate the interest so it matches this? .......
View 13 Replies View RelatedI 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?
A B C
1Loan Amount$10,500,000 0.0730041581143804
2Term 10 years
3Rate
4Amortization30 years
5Constant (K) =PMT(B3/12,(B4*12),-1000)*0.012
6Annual Payment$766,544
7Monthly Payment$63,879
Example is attached.
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 RelatedI 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.
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 RelatedI create this spreadsheet as a loan schedule using average daily balance method. (1/payment is constant, fortnightly 2/interest is 5.5% per annum)
In the interest column, at the beginning of each month ( when the day is 1) the interest will be added up from calculation of previous month daily balance.
My idea is that at interest column(let start at 1/08/2013) if (day(A49)=1, average the 30 or 31 cells above E49, 0).
I will manually make adjustment for February where 28 or 29 days applicable.
I need to expand on this formula.
I require a formula that will break down daily hours worked into rate categories eg Normal Time, Time & Half, Double Time.
eg. Column E = Total time worked
Column F = Normal time
Column G = Time & Half
Column H = Double Time
What I would like to do is enter hours into Column A and a formula in Column B will split of hours to a maximum amount of 7.6hrs then the remainder of the hours be placed in Column C to a maximum of 2 hours and Column D, no maximum.
These are the formula I am currently using
column B
=MIN(E2*1,7.6)
column C
=MAX(MIN(E2-7.6,2),0)
column D
=MAX(E2-9.6,0)
I would now like to be able to split the hours over 3 rows x 3 columns
Example 1
Row A B C D
1 3 3 0 0
2 5 4.6 .4 0
3 2 0 1.6 .4
Example 2
Row A B C D
1 6 6 0 0
2 21.6 0.4 0
3 0 0 0
The total hours in Column B cannot exceed 7.6 hrs
The total hours in Column C cannot exceed 2 hrs
There is not maximum for hours in Column D
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 RelatedSample data:
02-06-09 to 18-06-09 earned $2500
19-06-09 to 23-08-09 earned $3500
24-08-09 to 31-12-09 earned $4500
I am thinking if there's a formula that can calculate the interest earned for each month from Jun-09 to Dec-09.
I am trying to create a calculator based on a worksheet I have. I do not know how to write a formula for simple interest calculations and for compound interest calculations.
View 4 Replies View RelatedI 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
I need an array formula, that will peform a calculation in memory for each portfolio if the (Interest - B/F) = >20, or Excel Jeanie HTML 4
View 9 Replies View RelatedI am looking to calculate compound interest over a period of 10 years.
I am looking at putting a lump sum in at the beginning and contribute in monthly installments for the entire 10 years. How would I go about this.. I know there are formulas there, however I'm not a financial person at all..
I'm trying to come up with a way of calculating Money earned over time by compounding interest, but with a twist. After reaching a set amount of money, all money above and beyond does not gain interest. example:
Principal: $120 (user input value)
Duration: 9 (user input value in days, compounding daily)
%Intertest: 4% (user selected value, either 2% or 4%)
Max interest you can earn: $6 (fixed)
Max interest generating money: $150 (variable dependant on %interest, = $150 or $300)
Response/Answer is final value. I don't need the daily results like the example.
Result would be: $170.84
$124.80 (4.80 interest)
$129.79 (4.99)
$134.98 (5.19)
$140.38 (5.40)
$146.00 (5.62)
$151.84 (5.84)
$157.84 (6.00 reached the max interest level)
$163.84 (6.00)
$170.84 (6.00)
my equations I have so far only do one (below 150 total) or the other (above) but not both. and its just a regular formula: =IF(P<M,IF(P*I^D<M+1,P*I^D,"over limit"),P+6*D)........................
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 have been loaning my brother money over the past 14 months. The loans have been in the form or $1000 per month plus random payments for one-off expenses like doctors fees. He's not paid anything back yet but we want to know what the total owed is for interest of 10% per annum.
I can easily create a table with payments I've made and the dates with a running total of how much I've paid but how to I create a running balance of what he owes over time based on adding in interest. This might end with a one-off payment in a couple of months, I'd like to calculate what is owed there as a minimum.
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 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 Related