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

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 -

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

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

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

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.

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

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 RelatedI 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 Relateddoes anyone know how to calculate the interest so it matches this? .......

View 13 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.

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

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 RelatedI am having trouble with formula to calculate daily results.I can only calculate results base on daily plan.

please follow this link [URL] .....

Part A

2/12

3/12

4/12

5/12

6/12

plan

0

0

50

50

0

[Code] .....

I'm working on a time series dataset with a time step of 15 minutes. I need to calculate daily average of the several variables. So let column "A" be the "date-time" column, let column "B" be the "variable column" and column "C" be the "average column", I need a function that calculated in C1 cell the average of B1 to B95 cells, in C2 cell the average of B96 to B190 cells, in C3 cell the average of B191 to B285 etc.

View 3 Replies View Relatedcalculate overtime hours from daily time entries.

Normal hours are 7.6 per day

Time 1/2 is hours over 7.6 but no more than 2 hours

Double Time is all hours over that.

I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.

WedThurFriSatSunMonTuesTotal Normal HoursTime 1/2Double Time 10101068

I have a set of 7 years daily rainfall data. I need to calculate the daily average of rainfall from this 7 years data set. I don't wanted to calculate it by for example copying and pasting the first day rainfall of each year rainfall to a new sheet and average it for all the days. I need to calculate it all at a time.

View 1 Replies View RelatedI've set up a simple spreadsheet to keep track of my food expenses every month. The first column is for the date, the second is for the daily total expenses and then the next three columns are where I add the data which is then calculated into the daily total column.

I also have a total at the bottom for the entire month. Now what I want to do is I want to also have underneath the grand total, a cell which keeps track of my average daily expenses. Basically I want to divide the total expenses by the number of days which I've entered data. Now normally this would be fine but because I've applied the formula to all the cells in the expense column, it automatically lists every day as "0" rather than leaving it blank. So when it does the average calculation it's dividing my total by 30 days rather than by only the 7 days I have data for.

I currently have a profit/loss spreadsheet where I use a SUMIF to calculate daily gains/losses.

=SUMIF('Daily Sheet'!B:B,B377,'Daily Sheet'!H:H)

Basically, on the 'Daily Sheet' page when a sale is made I write it in a row. Each sale gets its own row. One of the column's has the date. (Column B) Column H has the respective gain or loss for that sale/purchase. B377 in this case contains the date 18-Nov. (B360 to B389 has Nov 1 through Nov 30) So this cell will calculate all the gains and losses in column H for each sale/purchase entry I have for that day. Some days I have 30 rows, others only 2 or 3, so this works great.

My problem now is that my rows have gotten much more complicated and detailed, and now I'd like to be able to only sum column H:H if it meets TWO criteria. (I'd like to add in Column C that specifies the type of sale or purchase being made, which is R, T or S for my purposes.) So I'd like to somehow write a sumif that only adds up the sales for 18-Nov AND sale type R.

I have a spreadsheet called PRODUCTION, where information is entered daily. I need weekly totals (mon - sun) to be extracted into another worksheet.

View 7 Replies View RelatedI'm creating an log to track the total number of hours an employee works in one day and calculate regular and overtime hours worked based on the following criteria: overtime will be >8 hours in one day and >40 hours in one week.

Right now I have it 'mostly' figured out, but under certain conditions, it calculates more than 8 hours in one day for straight time. Is there a way to set a maximum value for the straight time cell and have the difference be added to the overtime cell?

I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation. Currently, I'm using the following worksheet functions: =STDEVP(C2:C20)*SQRT(COUNT(C2:C20))

However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data. I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.

I have around 250 Employees Camp Meals Statements. Each day we prepare a Excell Sheet and enter the details file attached for easy reference Im manually calculating the Totals in each sheet if emp takes meals we marked as Y otherwise N based on that i want the total meals daily. One more thing Base on employeed code i want the monthly statement in another sheet same file attached..

View 9 Replies View RelatedI have the following scenario:

Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.

So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.

I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.

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

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.

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?

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

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

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.

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

ADVERTISEMENT