# 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
ADVERTISEMENT
Jul 23, 2009

Is it possible to arrive at the formula result in cell (C9) using only the cells in column A and bypassing the need to have a column B?

I originally tried =MIN(A1:A9)-MAX(A1:A9) as a way to bypass column B and have a single formula but it did not give me the same result. I was thinkging I might need to have a (UDF) but not sure. Thanks Again.

Column A ------------------------Column B-------------------------Column C

A1=2.53 ------------------B1=A1-Max(A$1:A1)

A2=3.52 ------------------B2=A2-Max(A$1:A2)

A3=5.47 ------------------B3=A3-Max(A$1:A3)

A4=6.87 ------------------B4=A4-Max(A$1:A4)

A5=7.89 ------------------B5=A5-Max(A$1:A5)

A6=4.14 ------------------B6=A6-Max(A$1:A6)

A7=3.23 ------------------B7=A7-Max(A$1:A7)

A8=2.10 ------------------B8=A8-Max(A$1:A8)

A9=12.21 ----------------B9=A9-Max(A$1:A9) -------------C9=MIN(B1:B9)

View 9 Replies
View Related
Aug 21, 2009

D17 is a cumulative dollar total (year-to-date) of the monthly changing dollar amount in D5. Each month I manually add the D5 figure to D17. Is there a formula that will automatically update the D17 cumulative total with the everchanging D5 monthly figure?

View 9 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
Oct 30, 2009

I have one cell that brings up a random number using RANDBETWEEN and I want to keep a running total in a separate cell, but I can't figure out a formula or function to do this.

View 6 Replies
View Related
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
Aug 24, 2006

I have been raken by brain for a simple formula to calculate an Interest Only Payment on a mortage note, example,

sales price = $162000

term = 360

interest = 8.5

I was able to calculate the payment of principle and interest, but I need to validate what would be an Interest Only payment on this?

View 5 Replies
View Related
Feb 20, 2014

If i borrow Rs.50000/- at 7.25% interest compounding monthly, repayable in 4 quarterly instalments, when i put it in excel using pmt function for emi quarterly payment and ipmt for interest calculation at the end of the 4th quarter (i.e last instalment) the balance will not become zero it shows a balance of Rs.14.35 its due to interest compounding monthly, is there any formula in excel to overcome it?

View 2 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
May 27, 2008

I am currently trying to create an excel spreadsheet where I would know how much interest I will pay in the next six months, since the balance changes every month I am not sure how to formulate the total interest paid with in a specified time period.

I currently can calculate the interest and fill in the below rows with each month but I would like to simplify the report so that I can add the Balance, Rate, Payment(2% of balance), and Time period and in another cell it will tell me how much interest I will pay in that time period. Bascially I would like to keep all the calculations in one row.

For example

Balance | Rate | Payment | Time Period | Interest Paid

$1,000 | 10.00%|$20.00 | 6 | $48.56

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
Jun 22, 2014

i am trying to create a spreadsheet that will automatically plan a days work in manufacturing based on the items the factory is making, the different items obviously have different hourly production targets and id lke to create a spread sheet that knows how many we want to create what the targets are and how long it will take. i also want it to be able to know when it has reached the needed total and automatically plot the targets for the next product, so what i need is a way to change part of the formula when the column reaches a set culmative total

View 3 Replies
View Related
Oct 24, 2008

I have attached 2 files. One of them is called Macca.xls. This is the file that was created for me by shg on an excelforum. The other isOreDep_updated.xls - this file is the updated list that I want to convert into the Macca file or make it look like the Macca file.

I awas trying to make a couple of plots, but they were not llooking like some examples I have.

In the OreDep-updated file attachment is my data I needed to use. I needed to make 2 plots:

Plot 1: Resource Grade (log scale) against the proportion of deposits (linear scale)

Plot 2:Tonnage (log scale) against proportion of deposits (linear scale)

This was done with my old data that is shown in the Macca.xls attachment.

They are meant to cumulative proportion plots. As a line or scattergram. An example of what they are meant to look like is at: http://www.nbmg.unr.edu/dox/ofr962/c11.pdf on Page 4 and 5.

shg managed to make them look like I wanted with the log normal smooth line too.

My problem now is that I want to change the data under Name, Resource Tonnes and Resource Grade (basically replace the old data in Macca.xls with the new OreDep_update.xls, to come up with same type of plots). But whenever I try to copy and paste the data into the Macca file it messes up everything. I looked at the formula that was created and I really havent a clue how to change it, without affecting everything else like the plots.

I also need to add 2 new columns for date and for reference, and it wont let me do that.

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
Oct 17, 2009

1. Can I lock particular cell If Yes,How? I want to lock cells which contains formulas.

2. If cell K7 shows 11 I want to see G11 in H9. Same if Cell K7 shows 201 I want to see G201 in H9.

Its a interest calculation sheet where K7 is number of days amount invested and COLUMN G shows interest accured.

View 3 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
May 12, 2009

I have a variable list of items in date order, there could be one, more than one, or, no items for any one date Each item has it's own line. I need a formula in a separate column (column F) to total each day's items. Column A includes the Date. Column F is a cumulative total (as in Column C) for items on the same date. Column C=D+E. I am looking for a formula which will add up items in column C if they have the same date in column A, and put the answer in the last cell in column F for that date. Example........................

View 5 Replies
View Related
May 29, 2014

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 Related
Jun 7, 2014

I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.

If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.

View 2 Replies
View Related
Jan 28, 2009

I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.

Example:

Breakpoint:

cum sales are > 500 pay at 3%

cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due

jan/ 100.00/ 100.00/ 0

feb/ 600.00/ 700.00/ 6.00

mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

View 9 Replies
View Related
Oct 6, 2008

I need a cell that holds a cumulative sum , but i get stuck into cell self reference.

Let me explain . Imagine i have cell A1 which holds a formula : =B1 + C1

If i keep changing the values of B1 and C1 the value in A1 will be changing as well . I need to the sum up of A1 values at any given moment. I practically want

A1 := A1 + B1 + C1

View 6 Replies
View Related
Sep 1, 2006

I would like to work out the cumalative value ffor each row on a sheet I have.

Basically it just needs to start from column 4 each time and then go across and take first value and then add that to the second one, take that answer and add to third one and so on. Basiaclly I need to get an end result on clolumn 25 each time. The code has to be able to count down the rows too.

View 9 Replies
View Related
Aug 14, 2009

Need a formula for cumulative value - i.e. total for month 1, month 1 and 2, month 1,2 and 3 and so on??

View 2 Replies
View Related
Oct 20, 2009

This problem has come at the end of a big exercise whereby I've managed to construct a spreadsheet that automates three payment processes based on various VLookups to another file. However, I've come to a stage where I need to pre-empt a payment allocation based on cumulative totals.

I'll try and explain clearly below but I've also attached a spreadsheet showing the intended result (along with a copy without the output so that someone can add in the formula - again, if it exists.

So, this all centres around a code allocated to our clients and a declaration that they complete. Say client code "Apple1" (col E) sends in instructions to pay on a position of "650,000" (col F). Within our existing spreadsheet we have an available position of "800,000" which can be seen by totalling column G for client code "Apple 1".

So that's the first requirement - for all rows on my existing spreadsheet for client code "Apple1", I want to add the cumulative total of column G ("800,000") I'd like this captured in column H, as per my example.

We can then make payment based on their declaration, up to their total available amount. However, we need to pay them in stages according to our existing allocations (col G). Therefore, taking the first example, their declaration show's an available position of 650,000, their total position is 800,000 and the available nominal amount for that row (col G) is 200,000 - so they can be paid on that full amount therefore, 200,000 should be shown in cell I3.

So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000.

The client "Apple1" now has a payment amount remaining of 250,000 remaining but in this instance, cell G5 is for 300,000 - so because the remainder of their available payment is less than the amount in cell G5, we should post the available payment amount in that field - 250000.

Therefore, if you add up all of column I for client "Apple1" it comes to 650,000 - the amount we have on our declaration - even though the total of their available position is 800,000.

I've included another client in the mix "Sauce2" who should show 200,000 and 0 respectively in cells I6 and I7.

View 14 Replies
View Related
Oct 28, 2009

Need to insert two rows, then sum column C and E based on cell value change on Column A.

...then also would like to get the summed value of each total eg,. (starting total should be where last cell should always equal the sum of first calculated cell. D6 = C6, then =C10+D6, and C16+D10, etc...) to be summed with next total down, and so forth to end (See attached)

View 4 Replies
View Related
Apr 13, 2014

I have a sheet that contains a reference number in Col A and an associated number of days in Col B. I wanted to put a third column in that cumulatively totals the number of days based on reference number.

View 3 Replies
View Related
Feb 22, 2008

After about 3 hours of online research, and maybe an hour or so of experimenting with the IF command, my mind is blown and I, essentially, have given up!

I have a spreasheet with each column representing a month, then four more colums to represent quarterly values, and one other for a summary of the entire year.

I don't want a cumulative running total. For example, if I enter a value for January, that value should appear in the 1st quarter column, and yearly summary colum.

Now, if I enter in a February value, the 1st quarter column should display the February value. I need the 1st quarter column to "update" for each of the three months within the quarter. Likewise for the yearly column except, obviously, I need it to update for each of the 12 months.

I started toying with the IF function, with limited success, but there MUST be an easier way?

View 9 Replies
View Related
Apr 18, 2009

Microsoft Excel - Book1___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB5=

BCDEFGH5JanFebMarAprMayJunTotal6-3%-2%5%8%5%5%???Sheet1

I have this example above which i want to know what is the total % change from Jan to Jun. In the total column, the result i got is 19% using formula

=1*(1+B6)*(1+C6)*(1+D6)*(1+E6)*(1+F6)*(1+G6)-1.

Is there any other more simplified or better formula that can be used actually?

View 9 Replies
View Related
Jan 19, 2007

in my worksheet i have different kind of items with its cost. in my case which is not in order, that is, the order of items can be AABAACCBA. I want to calculate Cumulated Total on each row. but i am not sure how to achieve this by conditional formula? the values in my sheet looks like the following,

Date ITEM TYPE AMOUNT Cumulated Total

10-Jan-07 BookA1010 -value(Book)

11-Jan-07PenA515 -value(Book+Pen)

12-Jan-07TableB1515 -value(Table)

13-Jan-07PencilA2035 -value(Book+Pen+Pencil)

14-Jan-07ChairB2540 -value(Table+Chair)

15-Jan-07SofaB3575 :

16-Jan-07RoseC2020 :

17-Jan-07Calc...A3065 :

18-Jan-07JasminC1030 -value(Rose+Jasmin)

find the attachment for reference. How to achieve this using conditional statement or lookups or someother? and i try to avoid macro.

View 4 Replies
View Related
Jan 5, 2008

Cells D 5 to D 36 are representing day 1 to day 31 of the month. clocked working hours of the employees will be entered to these cells. they are supposed to meet the target of 8 hours clocked at the end of each working day. that means on day 1 the target is 8 hours, day 2 it is 16 hours, day 3, it is 24 hours. i want to see the cumulative progress in ( percentage) on any give day. That means on each day the clocked hours should be compared with the actual cumulative target until that day.

View 4 Replies
View Related
Apr 6, 2008

I'm having difficulty with concatenation and the use of a string variable. On my attached spreadsheet I have a row called Must Fund Tasks. The binary variable in the cell is entered by a selection on a user form. If the value of the cell is "1" then that particular task must be funded. The code I developed forces solver to keep the binary value of 1 in that particular cell when looking for the optimal solution. This same row of binary variables is the row of values that I have solver change to find my optimal solution.

My problem is that I have another set of contraints based on mutually exclusive tasks. If there is a binary value of "1" in at least two of those cells then only one of the tasks may be funded and they are said to be mutually exclusive. In other words, if the user form indicated that tasks 1 and 5 were mutually exclusive then the binary values are Task1=1 and Task2=1 and the constraint would be Task1+Task2<=1. The code I've written looks for a value of 1 in the Mutually Exclusive row and if it finds one it adds the name of the corresponding "Must Fund" cell 4 rows above to a set called ExclusiveSet. ExclusiveSet is defined as a string and I'm having trouble with the concatenation, I keep geting a "+" out in front because of the intial loop when ExclusiveSet="" ( it returns "+$F$29+$H$29"). I'm also not sure if Range("CalcsStartcell").Offset(I + 15, 3).Formula = "=ExclusiveSet" is the appropriate way for me to return the value of the ExclusiveSet in the desired cell.

Sub constraint()

Dim p As Integer, CalcsStartcell As String, I As Integer, T As Integer

T = 7

I = 5

CalcsStartcell = "C16"

'setting Mutually Exclusive constraints

With Worksheets("Sheet1").Range(CalcsStartcell)

For p = 0 To T - 1

If .Offset(I + 12, p + 1).Formula = "1" Then..................

View 7 Replies
View Related
Apr 24, 2008

I'm guessing it will be some sort of nested loop but I'm just going round in circles trying to solve it. I need to calculate a cumulative value based on the following criteria:

1. Pre Qty - can be any integer value

2. Qty - can also be any integer value

Accumulate values by looking up the Pre Qty + 1 then add all quantities * values based on those provided in a table giving different to - from ranges. Attached is a spreadsheet

View 4 Replies
View Related
Apr 25, 2008

I have a list of daily sales which I would like to have a cumulative total by month. Once the next month is encountered, the cumulative total will reset for that month again.

E.g. (please see attached file)

Column C is my cumulative total. The cumulative sum will reset when the month changes from Jan to Feb.

I can achieve the results using VBA but I need to distribute my report to parties whose VBA environment is disabled. So, I need to work around this with a formula.

View 9 Replies
View Related
Jun 17, 2014

I'm trying to add cumulative values based on two criteria, one being a constant date, and the other being a date range.

I've attached the example where I would like to sum the highlighted values. I am trying to come up with a formula based on the criteria in cells I2:K2 but can't think of anything.

Example.xlsx

View 3 Replies
View Related
Dec 31, 2008

I have, for my formula, 2 constant values a and x, I then also have a variable, n. The formula I'm trying to convert into excel terms is

SIGMA (with respect from 1 upto n) ((1-x)^(1-n) * x)*a. Unfortunatly thats not drastically inteligable so i've attached a picture file that should explain it in more understandable terms. Now the problem im having is, sigma is a cummulative funtion, which in this example is relative to the variable n, firstly I don't know what formulas I would have to use in order to get a sigma/summination function, and secondly I am uncertain of how to link it to a variable range.

View 5 Replies
View Related
Sep 30, 2008

I have two rows one with the nr of weeks and another with revenues like that:....

i need to calculate the cumulative sum without inserting another row in the week that i define like an input so it could be changed.. for example the *** sum in the week 5 shloud be 900.

View 3 Replies
View Related
May 18, 2012

Basically, all I need to do is have a cumulative total in one cell that adds the same figure each fortnight. ie, adding 100 each fortnight would give me 500 after five fortnights.

Im assuming it needs to run off the date on my pc, and I've managed to figure that out with =TODAY(). What I cant figure out is how I would set a start date and then set my fortnightly figure to be added. Presumably then the formula would state "if today is 14 days (or divisibly by 14 days) greater than start date, add an extra increment".

View 7 Replies
View Related
Jan 25, 2010

I would like to be able to calculate cumulative returns by selecting a starting and ending quarter from a validation list and finding the product of all the quarters between the two selected. I imagine the equation would look something like this: ...

View 9 Replies
View Related
Feb 19, 2008

I have a table representing the usage of several thousand product in a market. Each row represents a product and one column indicates the frequency of use. I'd like to find out how many products represent the Nth percentage of the whole, when ranked from most used to least.

Shorter, if I want to know the Top 25%, how many products equate to the top 25% of all product frequency? ....

View 9 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