Interest Template: How To Sum Up According To Date
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
ADVERTISEMENT
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
Feb 21, 2013
how to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?
View 8 Replies
View Related
Jan 5, 2009
I Have a number(30+) of excel files based off of one template. I have since updated the template, thus making the the old ones obsolete. Is there a way to update the older files to use the new template without having to copy and paste the addition's row by row column by column?
None of the additions to the template will change the placement of existing data, nor will it change the data itself. It will however add Values to cells that were previously empty.
Hints, Tricks, or Ideas?
View 9 Replies
View Related
Apr 23, 2009
1)
I created a one-sheet template, and a new workbook from that sheet. When in that new workbook I go Insert>Sheet>Other and select the aforementioned one-sheet template, Excel crashes, or if not, it adds the new sheet, but then no longer saves the workbook and starts producing error messages (like: "An unexpected error has ocurred. AutoRecover has been disabled for this session of Excel.").
1a)
Now, the complication is that this is working when I do a model operation with a generic template sheet. So I checked the template that I actually want to form sheets after, and Excel finds no errors, nor does it's name contain any unusual characters. What could Excel prevent from working with a template like this?
2)
How do I edit a template? The only way I can find is to manually find the spot in finder, open, and save with the same name. But: if I do that, the documents basing on that template don't change accordingly. If they're intended not to, the whole template procedure makes no sense. I could then just as well copy a file. — I've been searching for tutorials on that, but google doesn't even return a single result on Excel "edit template". Therefor my very basic question here.
View 5 Replies
View Related
May 6, 2014
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
Master.xlsx‎
Template.xlsx‎
View 9 Replies
View Related
Aug 19, 2008
I have a list of invoice #'s on a sheet named "Temp Sheet".
I have a VBA macro that created a new tab for each entry and named it the invoice #. So basically the vba code created a new tab ( based on the number of invoice #'s on my list ), and named each tab an invoice number. So if I had a list of 10 invoice #'s, named S1-S10, the vba code created 10 tabs, named S-1, S-2,. S-3.....
Now to my question.
I have a template sheet I want to copy from ( "Template" ), and select any sheet that starts with "S", and copy/paste this template to.
View 9 Replies
View Related
Dec 18, 2012
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
View 1 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
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
View Related
Oct 13, 2011
How can I find the total of interest (or and expense) during a given calendar range
Data is as follows
Date Type Amount
4/1/11 Interest 4.00
4/15/11 charge 22.00
5/1/11 interest 3.00
I want a cell to compute the interest in april only.
I have tried =sumif(range, end date, amount_range)- sumif(range, beg date, amount_range)
but how do I nest the type argument into the argument
Possible approaches advanced filter? and function? add another column that uses an if statement to do interest only and do the sumif statement above I was hoping to have a drop down box that could have interest, COGS, beside it and april, may, june above it
View 1 Replies
View Related
Mar 6, 2007
Is there a function that calculates what interest rate is needed to turn a $2000 investment into $9000000 in 30 years based on compounding interest?
View 9 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 31, 2008
does anyone know how to calculate the interest so it matches this? .......
View 13 Replies
View Related
Jan 12, 2010
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.
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
Dec 1, 2006
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 4 Replies
View Related
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
Jun 29, 2008
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)
Course-rows which require action/follow-up are highlighted with a certain color. The "completed" course-rows 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 pop-up box of some sort which would display the "highlighted" rows (maybe from A through I or something).
I could add 'follow-up' flags on another column if the highlight color won't do.
View 3 Replies
View Related
Aug 14, 2008
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 2 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
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
Nov 8, 2008
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 2 Replies
View Related
May 10, 2009
=IF(AND(ISBLANK(A3),NOT(ISNUMBER(I3))),"",IF(TODAY()-A3>=30,G3*0.5%,"$0.00"))
How can I keep H from working if there is a negative balance in G? This will create negative interest.
View 4 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
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
