Calculate Compound Interest
Jun 30, 2007I 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
ADVERTISEMENT
Calculate Compound Interest Rates
Sep 22, 2011I 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‎
How To Calculate Daily Compound Interest
Apr 10, 2008I 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 -
Calculate Compound Interest Over A Period Of 10 Years?
Mar 5, 2013I 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..
Calculate Compound Interest With Capped Earnings
Dec 6, 2008I'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)........................
Calculating Compound Interest
Aug 6, 2006I 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?
Daily Compound Interest Using Two Rates
May 16, 2014Formula 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 RelatedCompound Interest Rate Formula
Jan 27, 2007I 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
Compound Interest Earned With Periodic Withdrawals?
Aug 3, 2014I am looking for a formula which will compute interest earned (not ending account balance) for the following scenario:
1) Set $ amount (say 19250) is deposited on January 1.
2) Annual interest rate is .95%
3) Interest is compounded daily
4) Fixed withdrawals of $1750 are taken each and every month on the first day of each month
What is the total interest earned on the account? Basically, it is a issue where each month diminishes the "pool" of money earning interest. I get what's going on conceptually, but can't figure out which Excel function to use.
Compound Interest Excel Table Formula
Dec 20, 2011I would like to have an excel table that has 240 usable lines going horizontal (to the right) and vertical (down) from the starting number.
I need it to compound interest in both directions
240 is the total number of days per year I am trying to track
There should be adjustable starting amounts and compound interest
Be able to adjust the entered amount anywhere along the time line and it recalculate the amount from there on...
Compound Interest After Mortgage Free Point
May 10, 2008I've found this calc but it doesn't compare to my particular scenario/goals: http://dinkytown.net/java/InvestmentDebt.html. The major difference is that this dinkytown calc requires a new loan to be put in place and I am not in a position to refinance my current mortgage. My plan is to pay an extra $267 (b3) to the mtg (b4). It will take 128 months (b6) to pay off the mortgage. After that point, I need to calculate what the newly freed capital (b5) would do if I put in an account (i.e. a CD or a simple US savings account) that had an Annual Percentage Yield (APY) of 1, 2, and 3% for 20, 15, 10 and 5 years. I thought that I had the right formula in place for cells b7:b8 should the account earn a 0% rate of return, but I think it's faulty since it gives me a negative number for 10 and 5 year accumulation periods.
View 2 Replies View RelatedUpdate Specific Cells Within A Column Used For Compound Interest
May 19, 2006I have a column of 30 cells, each showing retirement dollars earned based on years of service (cell 1 = year 1, etc) and salary. Here is the predicament: A new hire is brought in with 10 years service, stays 5 and then quits. How do I change only cells 10-14 reflecting his/her salary and retirement dollars? Remember, cell 10 is the first year for the compound interest, cell 11 the second year, etc. I currently use the following formula for cell 10 (compounded salary from cell 9 * (1+.05)^1). Cell 11 is (compounded salary from cell 10 * (1.05)^1.
View 5 Replies View RelatedCalculate Of Interest With Different Interest Rates Per?
Dec 29, 2013I 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.
Calculate Interest Per Month
Jan 12, 2010Sample 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.
Calculate Daily Interest Rate
Nov 8, 2008Some 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.
Calculate In Memory For Each Portfolio If The (Interest - B/F)
Jan 30, 2010I 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 RelatedHow To Calculate Interest Rate For Loan Product
Jan 31, 2014I 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
Table To Calculate Interest With Variable Withdrawals For Loan
Aug 2, 2014I 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.
Calculate 10% Or 20% On Investment Form Depending On Interest Rate In Column
May 25, 2009The 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 RelatedCompound Sum Or Subtotals
Sep 15, 2009Have a look at the attache example. I have inventory items which have a fixed value. I have several quantity columns to reflect various inventory positions. (OnOrder, WIP, ATP, ATS, OnHand, PAB as at date()...)
At the top of the sheet I need to show sum of quantities and sum of values. In order to compute the values currently I need to hide columns to the extreme right to do the math on a row by row level and then sum the rows and copy the the value to another cell. In this case the cells with yellow background.
Is there a way to be able to write a formula that would return the sum of the qty multiplied by the value without adding an additional column. I would need to function like with filters as well (like =subtotal())
Compound IF Functions
Aug 22, 2006See attached spread sheet as its a difficult one to describe
We are looking to apply labels to a cell depending upon criteria, the spread sheet contains the scenarios.
Compound Filter With Top/Bottom X
Dec 5, 2007I have selected a custom autofilter for one column of my data. After that, I want to use the top 10 autofilter in another column, but this appears to filter the top 10 overall, not the top 10 of the custom-filtered records. My data is transformer sizes.
First I custom filter the secondary voltage column to get only distribution transformers (less than or equal to 480). Now I want to know the 10 largest distribution transformers, but the top 10 filter on the size column makes all the records disappear. I believe it is selecting the top 10 overall, and it happens that none of the top 10 have secondary voltage less than or equal to 480. (When I change the custom voltage filter, I can get anywhere between 0 and 10 records for the top 10 by size.)
This seems to be an aspect of Excel, not an error. So far the workarounds I can see are:
1. paste the custom filtered data into a new sheet and then do the top 10
2. use the custom filter, sort descending by size, and count 10--but this problem was first brought up by someone doing the top 100, so this is not preferable.
Does anyone know a better solution that will make it easier to manipulate this data frequently?
Compound Annual Growth Calculation
Aug 8, 2006I am trying to calculate the compound annual growth and my starting point is a negative number. The example is that I have (273,000) for the YE 2003 and have Growth to the amount of +767,000 at YE 2006. The formula I have been using for other calculations where the starting point is positive is =POWER(J23/C23,1/O$1)-1. Where J23 = a positive amount and C23 = a positive number. This formula works fine, but when C23 = a negative number the formula does not work and the % does not make sense.
View 6 Replies View RelatedFunction To Work Out Monthly Compound Rate
Mar 16, 2009Im 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.
Interest Functions
Dec 3, 2005The 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
Interest Over 'X' Months
Mar 20, 2008I'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.
Interest Calculation
Apr 28, 2008I 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"))
Compounding Interest
May 6, 2008attached 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.
Cumulative Interest Formula
Nov 18, 2008I 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.
Interest Template: How To Sum Up According To Date
Sep 9, 2009I 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.