Amortization Schedule: Auto Update Based On Loan Period & Number Of Payments Per Year
I have uploaded a sample amortization schedule.
1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.
2. Also, if a value is entered in column E, then i require the whole table to update as well.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Loan Amortization Calculations
I'm working with a loan amortization worksheet (downloaded from Office Online). Unless I don't understand correctly, the date formula on the worksheet doesn't calculate the way I need it to. I'm not totally sure what the formula they use is doing. It does use a lot of named ranges. If a user inserts the total "Number of Payments per year", then I want the date to return the proper payment date. For instance, If the start date is 1/1/07 and the number of payments per year is 24 then the payment dates should be like 1/15/07 1/31/01 2/15/07 2/28/07 It should be the 15th and the last day of the month. If I put 52 as the number of payments then I want the formula to set the payment dates to every Friday. I'm still learning formulas so bare with me guys. Attached is the worksheet.
View Replies!
View Related
Is Loan Amortization Affected By The Currency
There are various references and links to " mortgage calculators;" though they are specific to the US dollar. Is the formula still the same, irrespective of the currency and why does it come across as quite a complex calculation? i have been taksed with designing a "calculator" and don't seem to know where to start as the currency issue is confusing me.
View Replies!
View Related
Amortization Table For X Year Mortgage
I'm trying to make an amortization table with all the individual payments. I'm trying to avoid putting in the formula manually into each individual cell. I have a formula = ROUND(F11*0.12/12,0) and I want to insert that formula down an entire column, but increasing the F11 to F12, F13, etc.
View Replies!
View Related
Auto Update A Number As In Accounting Packages
Is it possible to update a cell with an additional number eg from 22 to 23 when a template is opened. In accounting packages for example, every time you open an invoice it automatically increases the invoice number to the next higher number. I was thinking I might be able use excel to create an inwards goods receipt for items received for repair.
View Replies!
View Related
Week Number Based On Year Start 01/10
My finacial year starts on the 1st of October of each year and ends 30th of September each year. My week is from Monday to Saturday - though for calculation ease: lets say that my week is from Monday to Sunday. I need to calculate the week numbers of a date based on the above two criteria. I understand that I need to ascertain the day of the start date first i.e: did 01/10/07 fall on Monday, Tuesday etc. - In fact it was Monday! so for my year 07/08:Week 1 was 01/10/07 to 07/10/07Week 2 was 08/10/07 to 14/10/07ETCSo in the attached worksheet - what formula can I use to populate column D - by using the data in column E? I.e. All I want to do, is input date into E and B, C, D will be calculated automatically. It would be great of excel could populate A - aswell ;-)
View Replies!
View Related
Calculate Number And Amount Of Payments
The example: Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used. Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00 Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments. So the sort of result I'm looking for would be like Assume todays date is 19-04-09 A B C 12-04-09 £5.00 Last Week 4 Payments Value £45.00 12-04-09 £10.00 13-04-09 £10.00 14-04-09 £20.00
View Replies!
View Related
Employee Schedule Auto Complete In New Worksheet
This should be really easy, but for some reason I can't figure out how to make this work. I'm managing a resteraunt, and build my schedule by department using a drop down menu to insert my employee. I'd like to be able to automatically resort the data into a new worksheet organized by employee name. please see attachment.
View Replies!
View Related
Update A Formula Based On A Newly Inputed Number?
What i would like to happen is after inputing a number into say column B have that number update a formula in cell D2.... so for example... lets say i have B1 = 1000 B2 = 2000 B3 = 1400 B4 = ??? D2 = (B3-B2)/B2 is there a way to make it so when i enter a new number in B4 it will automatically update the formula to display the value for (B4-B3)/B3 and then continue on to repeat this process when i enter values into B5, B6, B7 ect?
View Replies!
View Related
Update Spreadsheet By 1 Year
I have a spreadsheet for Jan-Dec 2008 containing entries for each day as seen below. Mon07-Jan-08 Tue08-Jan-08 Wed09-Jan-08 Thu10-Jan-08 Fri11-Jan-08 Sat12-Jan-08 Sun13-Jan-08 Mon14-Jan-08 Tue15-Jan-08 Wed16-Jan-08 Thu17-Jan-08 I would rather not have to update each date manually so could any of you kindly inform me of a command whereby all of the 2008 dates can be updated to 2009?
View Replies!
View Related
Calculating The Number Of Business Days In A Specified Period
It should be deplaying dates of weekly days in Monday, Wednesday and Friday excluding sundays. Or Tuesday, Thursday Saturday excluding Sunday. e.g Mondays, Wednesdays and Fridays of June June 1, 2006 June 3, 2006 June 6, 2006 Tuesdays, Thursdays and Saturdays of June June 2, 2006 June 5, 2006 June 7, 2006 This should be happening after entering any date on the first cell of the List and should accommodate up to 3 months
View Replies!
View Related
Determining Number Of Specific Days In Period
I am wondering if there is a way in XL2003 to determine how many times specific days of the month occur within a specified date range. For example, I need to find out how many times both the 1st of the month, and how many times the 15th of the month occur in the time period starting 05/02/2009, and ending 07/12/2009.
View Replies!
View Related
Textbox Auto Update
I’m just starting to learn the VBA process with Excel, this problem is probably very basic so forgive me. I have a cell in excel that is linked to a textbox, if you add text to the textbox it will update the cell. If you close and reopen the application you need to click the textbox to show the value previously added to the cell, can this be viewed automatically. Here is my
View Replies!
View Related
Auto Update The Cells
I have been tasked with coming up with a solution to an excel issue my boss has. I'll try to explain it as simply as I can but it might get confusing. We have workbooks containing ledgers for a retail establisment. We create a new book for each year. Each book contains a sheet per month. We also have a comparison book/sheet. Currently we have the rolling total for each line item transfer over to the comparison sheet and then manually workout and enter the data for the same day from the previous year. What we would like to do is have the data from 2008 automatically update as we update the 2009 data.
View Replies!
View Related
Auto Update Edate
Ok this is tough to explain but I will try. I am using edate to find data that is older than 2 years old. I have a formula that works. =If(C2<EDATE(TODAY(),-24),B2,""). My issues is that it does not auto-update the data until I have manually changed the date. You can look at my sample. If you change the date or double clik on any "cert date" cell, then my data updates.
View Replies!
View Related
Auto Update Macro
I have a spreadsheet of 30 or so tabs, all of identical layout, with columns B to AS used. Each day I have to drag a row down to collect data from various other spreadsheets, on each of the 30 tabs (different data in each tab, but same format), i.e. today I dragged down B216:AS216 down to B217:AS217 for all 30 tabs. I could record a Macro on the first tab and then play it for the remaining 29, but I have to record the Macro each day so it drags down the next row down. I am after a Macro that will drag down the columns to the current date (which is in column A and is already entered down to the end of the year) on all tabs, so if I left it for 5 days etc, and then ran the Macro, it will drag down 5 rows. But if I had to run it 5 times that wouldn't matter. A button on the first sheet would be brilliant too, meaning I could open it up, click the button, and then all 30 tabs would drag down to todays date.
View Replies!
View Related
Auto Update Validation List
i have a list called "parts". i want this list to be updated everytime a new part # is entered into the list cell. i can get it to work in one cell. i have been able to get it to work in up to 6 rows, however i want it to work in up to 50 rows. here is the vba code i am using Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address >= "$D$3" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("parts"), Target) = 0 Then lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion) If lReply = vbYes Then Range("parts").Cells(Range("parts").Rows.Count + 1, 1) = Target End If End If End If End Sub i am using insert>name>define for the list and this is what i used for that =OFFSET(lists!$A$1,0,0,COUNtA(lists!$A:$A),1) like i said, it will work for d1-d6, then it stops working.
View Replies!
View Related
VBA - Auto Save / Update
i have a workbook that pulls data from 7 other workbooks based off of 3 criteria. In 6 of the 7 workbooks there are 6 sheets and in the last sheet there are 15sheet. All the sheets are used on different computers and currently the member who use these sheets cant remember to save them so the main workbook can pull the information. So I was hoping for a VB code to do the save for them after they enter any data in the range of A1:J50. Then for the main work book i was hoping for a VB code to auto update after entering the 3 criteria. I have a marco made but it takes awhile to do it. First it saves the sheet, then pulls the information, then refreshes some pivot table, then populates into the proper cells.
View Replies!
View Related
Vba Auto-update Colours
I am in charge of a report spreadsheet which gets updated by my users multiple times a day. The spreadsheet is called the R-A-G report (red, amber, green). One of the columns has colours which are updated manually in accordance to the ammount of stock we have in the database (oracle column) and in our warehouse (utl column). Right now the users update these colours manually, using a key at the bottom of the spreadsheet. My dream is to have these colours auto-updating when a macro is run. I spent a little time and wrote a very basic macro, but there are a few problems with it. For one i do not think that the macro fills out of the possible variables; therefor it has around 50% accuracy. I have uploaded the file and would really appreciate if i could get some feedback on how i could write this correctly, maybe some usefull websites specifically directed at elseif (if that is the correct code for me to use).
View Replies!
View Related
Vlookup From Another File Auto Update
I have a quote that has a value in a few cells =IF(B31="","",VLOOKUP(B31,'[Product Supply-1.xlsm]Sheet1'!$B$8:$N$11,13,0)). Though when i make changes in the product supply file i need it to automaticaly update the quote file. Look at thread http://www.excelforum.com/excel-gene...how-value.html to understand the problem. The quote file is there in thread.
View Replies!
View Related
Auto Sort Table Upon Update Of Another Cells
I'm using a total of 20 Rows and 2 Columns. Each row has Column A for Description and Column B for Score. There are total 10 subjects with 10 noneditable rows, and 10 editable rows. I'd like to protect column A and B for rows 1 to 10 and unprotect only Column B for rows 11 to 20. When any cells from Column B, rows 11 to 20 updates, I'd like to automatically sort columns A+B for ONLY rows 1 to 10. Therefore, rows 1 to 10 need to be sorted based on the scores on Column B, and since rows 11 to 20 are just input fields, they are never to be sorted. Column B for Rows 1-10 will be formulas and are based on Column B values from rows 11-20. When a value in any of the rows 11-20 of Column B is updated, Range A1:B10 will automatically sort from highest score to lowest score (Column B). Since Column B for rows 1 to 10 contains formulas and not actual values, would it throw off the order of things when sorting formulas? the macro coding to perform this task. Here is just a visual of what the spreadsheet will look like ....A B ======= 1||A (B11) 2||B (B12) 3||C (B13) 4||D (B14) 5||E (B15) 6||F (B16) 7||G (B17) 8||H (B18) 9||I (B19) 10||J (B20) 11||A 10 12||B 9 13||C 8 14||D 7 15||E 6 16||F 5 17||G 4 18||H 3 19||I 2 20||J 1 The Bold are editable fields. and I want to use auto sort A1:B10 based on updated values of B11 to B20. I have also attached the basic excel file that situates my concern.
View Replies!
View Related
Auto Update Links With Current Values
When copying data from a closed workbook, I frequently get the prompt: "Links to 'Source.xls' were not updated because 'Source.xls' was not recalculated before it was last saved. To update links with current values in 'Source.xls', click OK..." Is there a way I can bypass this prompt and automatically update with the saved (uncalculated) values and therefore not require a user intervention?
View Replies!
View Related
Pivot Tables: Auto-update 1 When Other Is Changed
I have 3 pivot tables on the same page - they all have the same PAGE and ROW fields, just different column and data fields. (may seem weird, but I'm producing reports for non-Excel people and this seems the only way to display info in user-friendly way). Problem: If a user changes the selection in a Page field, I need the other 2 pivot tables to automatically select the same Page field, so the pivot tables are still all like-for-like. I've started writing a macro which I can run to do this - below - but 1) I need it to run automatically when any of the page or row fields are updated 2) my macro is probably far too basic for my needs - one of the page fields has 245 values, so the below isn't really going to cut it!...
View Replies!
View Related
Auto Update Graph When New Data Is Added
I have a graph that is showing the date on "Y" axis and a value on "X" axis, when I add a new date and value to my data the graph does not update, it just shows the data when I first created the graph. How do I get the graph to display the new data I entered ? I created a line graph by clicking on the "A" in column "A" (Thats where my date is) then clicking on the "B" in column "B" (Thats where my values are) and clicking the create graph button then clicked finished.
View Replies!
View Related
Auto Update Of Data To Web Page
I did try a search before posting this - either I am too stuipid or the info was a bit too cpmplicated and in bits and peices for me to figure out. My query is this Lets say I have a workbook with around 10 sheets in it. and I have a saved a copy of the sheet ias a webpage (HTML ) for user convinence. Now is it possible to somehow ensure that when the orginal workbook is updated then this webpage would also get updated - both would be in the same drive ( I dont want to use internet for this purpose)
View Replies!
View Related
Auto Update Formula Reference & Sort
1. Is there any way a Cell can automatically be updated? for example: On Citi Bank worksheet, As soon as a deposit matures, I insert another row and enter new fixed deposit data on "Row 13", but then I have to manually change the references on "Row 4" so that it points to the new "Maturity date" and the "Interest" and the "Current balance". Is there any way I can automatically update Maturity, Interest rate and Current balance as soon as I enter new deposit information. 2. On the "Fixed Deposit Report" worksheet. Is there any way the list of deposits can automatically sort based on the maturity date as soon as I enter the new deposit information?
View Replies!
View Related
Auto Update Or Resync Values In Data Validated Lists
I was wondering if there was some way to auto update or resync values in data validated lists which are dependant on information in other cells. I attach my spreadsheet. My list in Cell F32 changes depending on what has been selected in cell F31. However, my problem is that, until you select cell F32, it still keeps the previous value on display, if you know what I mean. So! What I would like to happen, is when I select a different option in cell F31, then I would like cell F32 to update and show the first available option from the new list?
View Replies!
View Related
Auto-Update Validation List When New Entries Added To Source
how to make all cells displaying an item from the list source update with new information added to list source. Example: Cell "A1" on worksheet 2 displays the 3rd slot of information from list source on worsheet 5. I want cell "A1" on worksheet 2 to update when the list source information in the 3rd slot on worksheet 5 is changed. Notes: The list is set up by naming the columns on Worksheet 5 and is set as a validation list on the other worksheets. So on Worksheet 2 column "A" has the validation VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes". List=(Coulumn A 's name on worksheet 5)
View Replies!
View Related
Count Number Of Full Time Periods Within A Time Period
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...) I also have numerous set time periods that i'm interested in.. (about a dozen or so) for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00 What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed. So if my timestamps were Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2 and if my timestamps were Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps. and if my timestamps were: Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
View Replies!
View Related
Number Of Days Per Year
If I have a year in a cell, say 2006, and in another cell I need a formula to tell me how many days are in that year. Just to try and calculate deliveries of things far into the future, so need to account for leap years.
View Replies!
View Related
Amortization Sheet
I am looking for an amortization formula sheet for a boat loan that I have. That is easy enough, and I found that, but here is the hard part. I went in on the loan with 4 different people all paying different prices a month. Let me get specific - I have a loan where one person is paying $55 a month, another $30, and three more $35 each. For a total of $190/month for the boat loan. I want to be able to keep track how much each individual person owes in principal and in interest. Those prices a month are the minimum they will pay each month, but if someone decides to make more than the minimum payment, then it will mess everyone up with the total amount that they still owe. So basically even though it is more one big loan, it is actually a total of 5 smaller loans as well with the same interest rate, but different minimum payments a month.
View Replies!
View Related
Amortization Table Formula
I need help creating a formula. I have an amortization table and I need to take each months information and place it into a cell which resides in a letter. In the end, there should be 180 pages with different dates, interest, principal and total payments. I know that there is a way and I can not figure it out.
View Replies!
View Related
Date To Week Number & Year
I've done a search on here to find out how to convert a date to a week number & found this: - =WEEKNUM(A1) which works fine, But I also want the result to display the year. So 08/10/07 becomes WK40-07 I can't see how to do it!
View Replies!
View Related
Calculate The Number Of Paychecks And The Dates For The Calendar Year
I am needing to create a formula that will calculate the number of paychecks and the dates for the calendar year. There is some employees that are paid bi-weekly and some bi-monthly. For Example: A1-Employee, A2-Hire Date, A3-Pay Frequency, A4-Number of Paychecks Based on the hire date and pay frequency it will calculate the number of paychecks in A4. Is this something that can be calculate with excel or would it need to be done manually.
View Replies!
View Related
Number Of Months In Year 2007 From Date Range
I have a "start date" and an "end date". Is there any way to tell how many months are included in 2007 from those two dates using a formula? For instance, from 01-Feb-04 to 01-Feb-07, there is 1 month in 2007 (January). And likewise, 01-Jan-05 to 01-Jan-08 there is 12 months in 2007. As noted, I have the start and end dates. I don't have the "how many months in 2007". Start Date End Date How many months in 2007 01-Feb-0401-Feb-07 1 01-Jan-0301-Jan-06 0 01-Apr-0401-Apr-07 3 01-Feb-0301-Feb-06 0 01-Mar-0401-Mar-07 2 01-Feb-0401-Feb-07 1 01-Dec-0401-Dec-07 11 01-Jan-05 01-Jan-08 12
View Replies!
View Related
Debt Reduction By One Time Payment In Amortization?
How to calculate a One time Payment to maintain the Tenure In Amortization? Ex:- I havea loan of Rs 1,00,000/- to be paid @ 8%/12 for the first year and then from 13th month , it will be Floating Rate of Interest which would be mostly higher than 8%/12... I will be paying this loan off in 5 years ( i.e. 60 months) and the EMI = Rs 2028/- per month.. Lets say, if the Interest goes up to 12%/12 after the 12th EMI then the tenure increases by 5 months from 60 months to 65 months thus adding to the cost of Rs 2028*5 = Rs 10140 more.. Now What amount do I need to pay alongwith the regular EMI's to maintain the tenure of months? The amount Im referring to is a One-Time Payment added with the EMI for the 13 the mOnth to reduce the Principal and accordingly the tenure?
View Replies!
View Related
Loan Balance
I owe 15462 in the bank, currency dont matter here, that is what I owe right now, but I want to have a cell in the frontpage with the amount left, so can I make a line called =remaining-each month the amount should then each month be substracted from the new month and so on, until the amount is 0 can this be done? the second page in the spreadsheet has a post with monthly pays to the bank ...
View Replies!
View Related
Totals Based On Monthly Figures From Last Year
I need to make a sheet that give totals based on monthly figures from last year. Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75. One month Last year the store made 31.82% on it’s money. The bonus for anything over is $75 per 10% increments. Ie 0.00% $400 0.10% $475 0.20% $550 0.30% $625 0.40% $700 So this month was over last months, 37.18% so the bonus was $3,975. What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%. This hurt my head trying to get it right and im new to this more complicated formulas.
View Replies!
View Related
|
TRACKER
