Calculate Interest Per Month
Sample data:
020609 to 180609 earned $2500
190609 to 230809 earned $3500
240809 to 311209 earned $4500
I am thinking if there's a formula that can calculate the interest earned for each month from Jun09 to Dec09.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Daily Interest Rate
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.
View Replies!
View Related
Calculate Compound Interest With Capped Earnings
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 Replies!
View Related
Autocalculate Last Day For A Month
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days. Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month? IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29". The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).
View Replies!
View Related
Calculate Fiscal Month
I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.
View Replies!
View Related
Selecting A Month To Calculate Invoice
Ive created an invoice. The data is being entered on the previous sheet & then this invoice reads the data from the previous sheet through the formulas as shown below. The data that is entered, is entered contineusly everyday, so the invoice calculates the amounts from the entire data input sheet. I need to add some controll to the invoice where I can select a starting date & an ending date, and then the amounts must be calculated on the invoice according to the selected dates. H I J 27 Qauntity: Cost: Amount: 28 55 R 185.00 R 10,175 29 30 Net amount: R 10,175 31 Vat: R 1,424.50 32 Amount Due: R 11,599.50 Invoice [TableIt] version 09 by Erik Van Geit RANGE FORMULA (1st cell) H28 =SUM(Divenic!G5:G5004) J28 =SUM(H28*I28) J30 =J28 J31 =J30*0.14 J32 =J30+J31 [TableIt] version 09 by Erik Van Geit
View Replies!
View Related
Formula To Calculate Date End Of Prev Month
I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box. I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13. where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work. I would like a formula that will calculate these previous month end dates correctly. See example below ....
View Replies!
View Related
Date Function Query (calculate The Last Day Of The Month)
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank =IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5) The last day of the month function is shown below =DATE(YEAR(A13),MONTH(A13)+1,0) This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.
View Replies!
View Related
Auto Calculate End Date Of Previous Month
I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically? I enter in Cell 1: 1/31/09 Excel calculates in Cell 2: 12/28/08 I enter in Cell 1: 2/28/09 Excel calculates in Cell 2: 1/31/09
View Replies!
View Related
Calculate Working Days Left In Current Month
Is there a formula to calculate the working days left in the current month? I work in the financial services industry and am putting together a spreadsheet to automatically calculate sales production needs per "working day", based on my businesses schedule. We are open MF and Saturday, not including holidays. Now, I can look at a calendar, count the days left and put them in to the spreadsheet, but I'd prefer to have it calculate automatically. On my spreadsheet, I have used the =TODAY() formula. In the cell below, I would like to have it calculate the actual working days remaining in the current month. Since today is Sunday August 19th, I know that there are 11 working days left (starting Monday 08202007). Is there a way to get Excel to do that?
View Replies!
View Related
How Do I Create A Running Average That Will Only Calculate The Averages In % Each Month
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
View Replies!
View Related
Calculate Number Of Dates Within A Column Based On Month
I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything. So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.
View Replies!
View Related
SUMIF Function: Calculate What Is The Total For An Individual For The Whole Month
I have the same data (but not necessarily on the same cell for everyday) for everyday of the month. Now I need to calculate what is the total for an individual for the whole month. Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work. =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7). I have simplified the file as an attachment below.Appreciate need as the original file is for 6 months and I need a way to compute the data.
View Replies!
View Related
Calculate Work Hour Phone Time By Day, Week & Month
I am trying to figure out a way to count the minutes used from 8am5pm MondayFriday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.
View Replies!
View Related
Interest Functions
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 Replies!
View Related
Interest Over 'X' Months
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 Replies!
View Related
Interest Calculation
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 Replies!
View Related
Compounding Interest
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 1011 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 51015and 20 years.
View Replies!
View Related
Interest Template: How To Sum Up According To Date
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 Replies!
View Related
Compounding Interest Formula
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 Replies!
View Related
Calculating Compound Interest
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 Replies!
View Related
Cumulative Interest Formula
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 Replies!
View Related
Display Rows Of Interest Only
I have this sheet at work which contains a bunch of courses my employer runs, alongwith the dates and other details (which spans columns A through O) Courserows which require action/followup are highlighted with a certain color. The "completed" courserows have no fill. Note: the courses requiring action may have a past date, the only distinguishing feature is the highlight color. Is there any way by which i could generate a popup box of some sort which would display the "highlighted" rows (maybe from A through I or something). I could add 'followup' flags on another column if the highlight color won't do.
View Replies!
View Related
Interest Only Payment Formula
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 Replies!
View Related
Bank Interest Calculator
I'm an amateur to macro as I'm only in a low level class at a university... But I'm attempting to make a macro for a bank interest calculator. It asks your type of account(which then assigns an interest rate to it), how much money is in the account, and also how long th emoney will be in the account. I used a "Select Case" for the account types, but I seem to be struggling for it to work, it won't put the value of the total into the assigned cell, or it's just not computing it(as I get "0" each time I run it)... Public Function BankCalculator() 'Bank Calculator for different accounts 'declare variable Dim shtBank As Workbook, strAct As String, intMon As Integer, strLong As Integer, intTotal As Double Set shtBank = Application.Workbooks("Bank Calculator.xls") 'input box for amount of money, assign address intMon = InputBox(prompt:="How much money do you currently have in the account?", _ ......................................
View Replies!
View Related
Show Only Records Of Interest
Following is the excel database. B11 is the dropdown menu. When I select the team from drop down menu. it shud give me sorted Name list from B12. I tried the following formula: =IF(INDEX($C3:$E$11,MATCH($C$12,$D3:$D$11,0),2)=$C$12,INDEX($C3:$E$11,MATCH($C$12,$D3:$D$11,0),1)," ") But it is repeating the name or leaves the cell blank ( which i don't want). When I select Team1 it shud give me Names : a,d,e (without blank cell), Even i tried Array and some vba programmimg but it ........... ABC 1SrNameTeam 21aTeam 1 32bTeam 2 43cTeam 3 54dTeam 1 65eTeam 1 76fTeam 3 87gTeam 3 98hTeam 4 10 11Team 2 12b 13b 14#N/A 15#N/A 16#N/A
View Replies!
View Related
Quarterly Interest Calculating Formula
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 Replies!
View Related
Compound Interest Rate Formula
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/P1) 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 Replies!
View Related
Grid To Determine Interest Rates
I have a grid to determine interest rates. The are add ons to the rate that are dependent on two factors  the credit score and the Loan to Value percent or LTV, both of which are shown in terms of ranges (720739, etc). See the screen shot below. I have already written formulas for converting the score and LTV to the ranges as they appear on the sheet (ie, if you type in a score of 722, the formula converts it to the range of 720739. What I need to do is this  when the score and LTV are input and determine which set of add ons come into play, I need the sheet to take those addons, identified by an x next to it at the top of the page, and add them to the base interest rate, giving me the final rate. Again, see below to make this more clear. I've thought of using VLookup, but I don' think that would be applicable here. What's the best method, and can you give me an example of what a formula might look like? Loan Level Pricing Adjustments Base Rate 6.25 6.25 Credit Score 741 >740 LTV 60 740 Cash Out 0 0 0 0.25 0.375 0.375 n/a >740 Investment 1.5 1.5 1.5 2 2.5 2.5 n/a >740 2unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 >740 34 unit 1 1 1 1 n/a n/a n/a 720739 all 0.25 0 0 0 0 0 0 720739 Cash Out 0 0.125 0.125 0.375 0.5 0.5 n/a 720739 Investment 1.5 1.5 1.5 2 2.25 2.5 n/a 720739 2unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 720739 34 unit 1 1 1 1 n/a n/a n/a
View Replies!
View Related
Formula For How Much Interest Would Be Paid In A Given Timeframe
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 Replies!
View Related
Canadian Mortgage Calulator  Interest Savings
I'm attempting to build a Mortgage payment calculator which calculates interest semiannually. The goal of the calculator is to determine how much interest you would save if your payments were set to Accelerated Biweekly, and Accerlerated Weekly vs. Monthly. I've already calculated what the payments would be for each payment type, however I'm not able to determine what the interest savings would be.
View Replies!
View Related
Effective Rate Calculation, Interest Only Loan
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.
View Replies!
View Related
