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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 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
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
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
Multiple Loan Repayment Optimization
I have 4 Loans of various interest rates, balances, and minimum payments. Assuming I have a certain amount of money to pay out each month, how can I minimize the total amount I pay over the lifetime of the loans? Given: Total Monthly payment: M Interest Rate for each loan: R1, R2, R3, R4 Initial Principal for each Loan: P1, P2, P3, P4 Minimum Monthly Payment: Min1, Min2, Min3, Min4 Each month, how should I distribute M over the 4 loans?
View Replies!
View Related
Calculate Home Loan Repayments
I have used the PMT function but this gives me the total to pay per month. I want to know what the repayments would be and also allow me to add in each one of the periods and extra repayment option. So if in the year I wanted to pay an extra $100 per month, I would put $100 next to each period as in a particular period(s) I might not have to put in there but want to predict what the amount owing on the house is. Is this possible or is this too complicated.
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
Calculate The Monthly Repayments On A Loan Taken Out Over A Certain Amount Of Years
I need to calculate the monthly repayments on a loan taken out over a certain amount of years, which I can do fine. I just cant get my head around how to calculate monthly repayments over a certain amount of years when the intrest is compounding annualy. What I have so far: p*(1+(r/100))^n Where p is value of original loan, r is annual intrest rate, n is amount of years, and I am hoping I am right in saying this is the total repayable amount of the loan? Then putting that aside I created a amortization table. (which I am certain i forgot to include compound intrest in!) To keep it short i followed this guide for the amortization table. and now I am so confused about if I should be using PMT, PPMT, NPER?!
View Replies!
View Related
Loan Processing-Copy Range From 1 Sheet To Same Range On Another
I'm developing a loan processing system for members of a club. When an applicant asks for a loan, the club will calculate 10 % of that interest and the applicant will have to pay it back in 5 successive fortnightly instalments. If he asks for a loan in the first fortnight (1), for example, he will have to start paying instalments in fortnights 2,3,4,5,6 to pay it all back. The system currently has 4 worksheets. The first sheet is a the loan application form. The cells outlined in thicker border, are the cells in which details must be input. Once it is input, the data will be automatically placed in the Processing worksheet using IF and VLookup functions (See spreadsheet attached), which is used as a basis for the loan schedule Worksheet. What I need is a macro that will copy the range filled in the Processing worksheet, and copy it to the exact same location in the Loan schedule worksheet (The cells with the same fortnight columns and the same member name. This is how the loans are to be filed.
View Replies!
View Related
Userform Calculations
im in the process of designing a userform but it needs to have some calculations in it, and im lost on how to do it if anyone can help ie in textbox1 there is a value of 5 and in textbox2 there is a valuve of 10 and in text box 3 i want to appear textbox1 multiplied by textbox2 so 50 should appear this is a basic example but as soon i have mastered that i can adapt everything else to my userfom will the above be done automatically as soon as i enter values or would i have to "make the calculation" via a button or something and also i want to have a enter button or something like that that copies the data that i have put in the various text boxes to the excel spreadsheet and clear the userform cells phew thats best i can explain any questions just reply to this post as per the norm
View Replies!
View Related
FIFO Calculations
I have inventory... with starting product at a certain cost, received product at a new cost, and used product. I want to assume that we are using FIFO. What I need is a total cost (what I have paid) for what is sitting in my freezer. Column A is START (5) Column B is RECEIVED (6) Column C is USED (2) Column D is END (9) or (A1+B1-C1) Column E is OLD COST ($12.20) cost per unit of those 5 Column F is NEW COST ($13.50) cost per unit of the 6 i got in So I need in Column G a FIFO formula for total cost of what I have in the fridge.
View Replies!
View Related
Reverse Calculations ..
I dont know if that is the correct title to use but here goes. I am trying to help my friend with some work that he is struggling with. We have a model where we can change the % of the Service Level in field E8 and it will tell us the number of people required within field E17. Is their a way we can reverse this by creating another spreadsheet where we could put in the number of people we have for it to tell us the service level that would reach?
View Replies!
View Related
Turn Off Calculations
I have attached a sample file. All the data is inserted into this file via a text file, except one column "G". Someone in this forum was kind enough to help me in being able to include the city name 'G' to a parcel number 'A'. However, the page is constantly trying to complete 'Calculations' and won't let me do anything without first hitting 'Control Break'. This is fine except that when I try to save the file into .txt, I don't have the option of 'Control Break' and I have to end up closing the file. As the 'real' file has over 100,000 rows, nothing happens very fast. I have tied changing the 'Calculation Options' but that doesn't seem to change anything.
View Replies!
View Related
Slow Calculations
I'm working working with this spreadsheet that is moving incredibly slow. Every time I enter anything, it takes anywhere from 10 seconds, to a couple minutes to calculate and let me proceed. It is a pretty big file (4.60 MB), but I also work with another spreadsheet that is a little smaller (2.95 MB) that has never taken more than a fraction of a second to calculate anything. What could I do to spead up the spreadsheet?
View Replies!
View Related
Time Calculations.?
if you look at attachment, in Cell J2, if cell I2 is greater than 2 minutes but less than two minutes, the I need the value of I2 in Cell J2 otherwise 0:00:00 then K2 between 3 & 4 Mins and so on.
View Replies!
View Related
Hide All Calculations?
can i hide all calculations inside my excel document? I wish to provide some excel worksheets but must protect the calculations performed for privacy reasons. I would still like to enable clients to sort tables, change pivot tables, etc but not to see what calculations are used.
View Replies!
View Related
Changes To Status Bar Calculations
Does anyone know of a site or some code that can make changes to add some items to the status bar calculations? Such as right now you have Sum, Count, Count Nums, Max, Min... just to name a few. I'd like to be able to add some other ones down there if it can be done. I'd like to count #N/A's or possibly sum only positive numbers? I can do it with a quick keyboard shortcut and message box with a macro, but I'd like to just be able to highlight a range and have it show up like sum or the others do. Just thought I'd psoe the question.
View Replies!
View Related
Performing Calculations On My Comments!
i've managed to get a set of cells to show (with comments) what the last weeks data looked like. (so cell value = this week, comment contains last week) What i now want to do is to use conditional formatting to highlight any major differences between the figures... so: My comment i'm sure is stored as text in the format: Previous value was xxxxx So the first thing i'd need to do is to to strip out the text at the start, and then perform the calculation.
View Replies!
View Related
Cross Matrix Calculations
I have 2 tables. One has a listing of items and the work centers that they use and their associated hours. The other has the items and the amount we will produce each month. What I need is to be able to calculate the number of hours for each item by month and then make a summary so I can see the capacity needed for each item, work center by month. I have used the VLOOKUP and it works but when I overwrite the second table with new data the calculations get messed up
View Replies!
View Related
Inventory On Hand Calculations
I have a simple inventory spreadsheet that is doing everything I need minus one function. I have not been able to figure out how to make this work yet, maybe I can get some help. We remodel apartment complexes. I purchase material and hand it out to the contractors. I am using one page to track all movement of the items, but I have another page that I want to do that math and tell me what I should have on hand. Example: (On sheet1 "movement") I hand out 20 outlets. On my inventory on hand sheet I have a line that says "Outlet", the current quantity is 40. I have a drop down list so that on the Movement page I select "Outlet" in column A and in Column B I put in the quantity. I want to keep a running total on my IOHand page of everything, so that it would automatically subtract the 20 I just handed out and leave my IOH as 20. I have about 100 items on my IOH page. I assume I need a formula in the quantity cell of my IOH page that will search the Movement page and look for a match to what is in column A and Sum all of my "outlets" or "sinks" into the right cell.
View Replies!
View Related
SUMPRODUCT Causes Slow Calculations
I made one excel file with 9 sheet. there are used sumproduct formula in 200 rows. i am not used any vba macro for this file. i am used sumproduct formula link to other files and other sheets. my file size is 1.80 mb but when i am enter new data in file then file going in process of calculation and calculate all forumula it take some time. my file options calculation is automatic but for calculation it take time and i have to wait for that for Process. There are any macro for calculation so my file work fast and don't want to wait for calulation process . This file is not open like other files. it is take a time in update the link or calculation.
View Replies!
View Related
Medical Insurance Calculations
If H6 amount is greater than E8 then the answer in F8 will be the same as H6. If not, F8 will be the difference between H6 and E8. The above will be used for the deductible amount as follows: If patient A has a $1,000 deductible and the allowed amount for the product is only $250.00, then all of that woudl be the patient responsibility. However, if the patient has a $250.00 deducble and the allowed amount is $1,000 then the patient would only owe the $250.00 deductible. Is there a formula that can decipher this?
View Replies!
View Related
Summing Multiple Calculations
I want to be able to sum across a worksheet the products in individual rows of a $ rate and a qty, without doing this for each column and then adding them. for example: I might have in B1 '$100' and in B2 '3', then in C1 '$200' and C2 '6' and so on. What formula can I put in A3 to sum B1*B2 plus C1*C2 and so on?
View Replies!
View Related
Stop Calculations Upon Startup
when I powered up my workstation, the workbook has reverted back to calculating upon startup. I have provided a sanitized version of the workbook. I think the issue with the calculations during startup may not be apparent to you due to your inability to access the Access database this workbook extracts data from. I attached a test database which should hopefully work correctly with the workbook to demonstrate the calculation issue. Extract and save the test database in c:emp. That is where the workbook import queries will look for it. The live workbook is much larger and on a remote server so the calculations take a lot longer than what you might see here.
View Replies!
View Related
Percent Change Calculations
I want to calculate percentage changes, but sometimes my values are negative. Using the traditional (latest-first)/first I'm getting incorrect percentages because of the negative values. How can I write one formula that corrects for this?
View Replies!
View Related
Time Sheet Calculations
Attached is a layout that I am trying to get working. the 1st tab is the input sheet, I want to be able to post input on that sheet and have it transfer to the other tabs in the appropriate fields. I'm having a problem mostly with the vlookup to get the data into the right cells on the other tabs.
View Replies!
View Related
Combo Box With Calculations
I have a Userform set up and I have a combo box which I have filled with options using the With Combobox.add method with 5 choices; Minutes, Hourly, Weekly, Monthly & Yearly. I want these choices if slect to represent a value to make this easier say if minutes is slected the value would be 1, hours would be 2 etc.. I want this choice to be stored as a variable say time, which can then be later used in a calculation which is run when I run my main program based on this selection.
View Replies!
View Related
Mortgage Calculations With A Difference
Scenario - I started doodling with buying a property to let and then started thinking about adding the "shortfall" between the required bond payment and the income generated by the property back onto the loan each month as the shortfall arises. Let's assume that the financial institution will "pay" this shortfall from a previously agreed upon additional amount available on the property - understand? While there is a shortfall between the income generated by the property and the bond payment, insurance, taxes etc., the outstanding capital will obviously increase as each new month arises and each specific shortfall is added onto the outstanding capital (loan at the bank). Once the shortfall turns into surplusses, the surplusses are in turn paid into the account until the bond is paid in full. With a normal, say 15 year, bond, the interest payments and principal payments of the constant mortgage payment (assume fixed interest rate) nicely works out to a balance of 0 in month 180, but not in the scenario I describe. What happens in the scenario I described above is that, if I keep the payment constant (=PMT(and the rest - calculated on the original term and the original investment) each month's deficit is added to the outstanding capital but the payment stays constant. I want this payment to remain constant throughout the bond.........
View Replies!
View Related
#VALUE! Error With Date Calculations
I have 2 columns of dates and would like to find the difference in 2 dates in column a and b for example. when I use the formula a2-b2 I get #Value! or # Name? in these cells. The cells appear to be in format dd/mm/yyyy. I tried formatting them to mm/dd/yyyy, but the dates are not changing at all. I have quite a few of these spreadsheets so it won't be practical to retype each cell individually.
View Replies!
View Related
Calculations Via True And False
I am currently looking at the workings of a spreadsheet designed by someone else. Within this sheet, a cell has the formula that , once broken down, equates to: =100*TRUE (Cell returns a value of 100) And another that equates to : =100*FALSE (Cell returns a value of 0) Am I correct in thinking that TRUE always equals 1, and FALSE always equals 0? I think this is right, but just need someone to confirm.
View Replies!
View Related
Reverse Mortgage Calculations
I set up a formula knowing only two variables? 1. Assuming I know how much per month one can pay in total, and 2. The percent interest rate ... I need the total amount that can be financed. Example: A. $3,900 maximum per month B. 5.00% interest rate C. = x (the amount to be financed)
View Replies!
View Related
Speed Up Vlookup Calculations
I have been assigned to speed up vlookup calculations that reference several workbooks. The spreadsheets currently take a very long time due to the enormous amount of calculations. A typical example of the vlookup formulas is on the production tab of the attached excel file. We use 12 different workbooks that refer to each other and up to about 10 worksheets in each workbook. I've read a little bit about optimizing vlookup and using other methods, but I'm not sure how to apply it to my case where it refers to other workbooks. Any help you could provide in giving me a place to start would be greatly appreciated (i'm not even sure if excel formulas or vba code would be the best approach).
View Replies!
View Related
User Interface For Calculations
I am trying to create a user pop-up box that can accomplish the following: a. Pop up box that requests an input for a particular machine number, type of error and also a particular calculation type (STDEV,Average,etc). b. The result will be displayed either in a tabular format or if applicable, a chart I have 10 worksheets where each worksheet is a different mahcine ID. Contained within the worksheets is various data-sets(Data listed by week). I am trying to enable my mangers ability to not have to look through my data or choose a worksheet within my workbook. I would rather have a input screen that would give him a choice.
View Replies!
View Related
Restrict Calculations By Sheet
My 10 MB excel file has calculation mode always automatic. It has 100 worksheet. Only a few of these worksheets are enable for calculation, for performance optimization. Therefore, I use the macro: Worksheets("sheet1").EnableCalculation = False When needed, I do: Worksheets("sheet1").EnableCalculation = True Unfortunately, from time to time, the calculation does not work. By chance I realize that by insterting a cell in this worksheet, the EnableCalculation became active. My problem is that I feel this is happening randomly, depending on worksheet, maybe depending on users or on sequences of entries.
View Replies!
View Related
Calculations Between Two Pivot Tables
I wonder if it is possible to make calculations between two pivot tables which have the same number of columns and rows and put the outcomes in a third pivot. For example Pivot 1 contains revenue data, Pivot 2 contains cost data and Pivot 3 provides the calculated outcome of revenues minus costs.
View Replies!
View Related
Time And Cost Calculations ..
I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit. The source data I have is; 1 - Start time and end time of the job 2 - Any breaks taken during the job 3 - The number of staff it took to do it 4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates 5 - The number of punnets packed. With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning £7.50 an hour worked for 1 hour 15 mins at a cost of £0.39
View Replies!
View Related
Populating A Table For Gear Calculations.
I have a list of numbers (each corresponding to a different available gear). I have four columns and am trying to populate those columns with all the possible variations without using the same gear twice in any one row. Is there any way of doing this automatically or with a script or something? There are 16 different gears so it could be rather time consuming to input all variations manually. The attached worksheet has what I am trying to do begun. The possible number of iterations or variations is apparently enormous and am hoping for an automated way to populate those cells.
View Replies!
View Related
Weekly Time Sheet Calculations
I need to calculate the total hours worked for a series of 22 locations. Each Location has weekly allocated hours ranging form 0 to 80. For example: User1 worked 4 Hours on Monday at Location1 and 4 Hours at Location2. User2 worked 4 Hours on Monday at Location1 and 4 Hours at Location3. I am assuming I will need to use VBA for this calculation.
View Replies!
View Related
Evaluate() For Loop Less Range Calculations
I'm trying to get my head around using this syntax for EVALUATE() to allow an entire range of values to be changed all at once instead of via loop. This macro works: Sub TruncateValuesInSelection() 'JBeaucaire 2/23/2010 'Select a range and then run the macro to truncate values without looping Dim Cell As Range With Selection.Cells .Value = Evaluate("IF(ROW(1:" & Selection.Cells.Count & "),TRUNC(" & .Address & ",2))") End With.........
View Replies!
View Related
Ignore Number Values In Calculations
I've tried changing the cell format of some number values to text, so the numbers are not read and calculated in to certain formulas, but they're still included in the calculation. I need all values to display in the spreadsheet, but I need exclude a range that would vary. Is it possible to just change the cell format, so numbers are ignored by a formula?
View Replies!
View Related
|
TRACKER
