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
ADVERTISEMENT
Jun 30, 2007
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 Related
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
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
Apr 10, 2008
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 -
View 9 Replies
View Related
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)
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
View 9 Replies
View Related
Aug 3, 2014
I 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.
View 4 Replies
View Related
Dec 20, 2011
I 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...
View 2 Replies
View Related
Mar 5, 2013
I 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..
View 9 Replies
View Related
Dec 6, 2008
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)........................
View 2 Replies
View Related
May 10, 2008
I'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 Related
May 19, 2006
I 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 Related
Oct 21, 2009
I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.
But I want to make it compact , as d one I created is long enough.
A3 = Principal Amount
B3 = Date of Investment
C3 = Interest as on Date
D3 = Number of Days , amount Invested {comes out of formula set}
E3 = Rate of Interest
Now in F3 I want the Interest amount , compunded quarterly.
Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?
View 8 Replies
View Related
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
Feb 4, 2014
I have developed a financial calculator that asks the user for the "input date" which is used to record balances as of the input date. My interest calculation for the first year is based on the current date compared to the input date. For example, if the user is keying in a current balance of 10,000 @ 10% interest, and the "statement date" or "input date" is 12/30/2013, and the current date is today, 2/4/2014, then it should calculate interest for the entire year of 2014. It is not doing that. It calculates $3 interest.
But if input date is 6/30/2013 with current date of 2/4/2014, it seems to work OK. It calculates interest of $504 in that case. It appears to get messed up with the year transition between current date and input date. The formulas I have listed below appear to work fine except when the input date is 2013 for the year and the current date is 2014. The formula does not "see" that input date was last year. There must be a minor tweak to formula I am overlooking.
Cell C2 = Today's Date=TODAY()
Cell C3 = Input date (user keys in date in mm/dd/yyyy format)
Cell E2 = "translate input date to year format" =DATE(YEAR(C3),12,31)-C3
Cell E3 = Investment Rate
Cell G2 = yr 1 interest rate adjusted =(E3/1)*($E$2/365)
Cell C21 = Current Balance
Cell D21 = Interest Yr1 = C21*G2
I need the interest calculation to account for partial year accrual.
View 3 Replies
View Related
Feb 20, 2014
I've uploaded my spreadsheet. I asked my professor if our formula had to take into account whether or not our payment would be changing. As long as I calculated the initial amount - which worked out to be around 30 dollars, I could assume I paid that amount for the entirety.
ASSIGNMENT 4
My latest Mastercard statement shows a balance due of $1,696.96.
Based on recent bills, assume that the minimum payment amount is computed as follows
(balance_owed)*(annual_interest_rate/12) * 2, then rounded to the nearest whole dollar amount.
The minimum payment amount is the amount calculated above or $25.00, whichever is the greater amount.
The annual interest rate for purchases is 11.24%. If I make no more purchases and always pay my bill on time how many years and months will it take be pay it off at the current minimum payment amount each month?
How much total interest would I pay if I repay the loan this way?
What fixed monthly payment amount would pay off the debt in 2 years?
Assume monthly compounding of interest. All calculations are to be done in the Excel worksheet.
The functions NPER() and PMT() will be useful. Note that either the Present Value or Payment amount should be negative (not both) because of conventions concerning the direction of cash flow."
View 3 Replies
View Related
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
May 21, 2013
I 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.
View 1 Replies
View Related
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
Sep 15, 2009
Have 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())
View 3 Replies
View Related
Aug 22, 2006
See 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.
View 4 Replies
View Related
Dec 5, 2007
I 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?
View 3 Replies
View Related
Aug 8, 2006
I 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 Related
Mar 16, 2009
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.
View 2 Replies
View Related
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
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
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
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
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
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
