Debt Reduction By One Time Payment In Amortization?
Mar 14, 2009
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 14 Replies
ADVERTISEMENT
Feb 25, 2013
I am using Excel 2003 and I created an amortization schedule set up for an debt account. I am trying to pull the "Balance Due" from that schedule into another chart based on the current date (these are on two different sheets in a workbook).
For example, this is my 'Amortization Schedule':
Balance
Due
Interest
Rate
This Month's
Interest
This Month's
Payment
[code]....
And I am trying to pull the "Balance Due" from that schedule to place into this chart on my 'Debts' sheet: (based on the current date)
Name
Starting
Balance
Remaining
Balance
Interest
Rate
Minimum
[code]....
For example, if today were 1/15/13, I would want $3,796.34 from the schedule to go where the "x" is on the chart above. What formula would I use to accomplish this?
Also, on a side note, would there be a formula to have Excel pull the "Payment Date" from the schedule into the "Payoff Date" in the chart based on where the row has a $0 Balance Due?
View 2 Replies
View Related
Nov 18, 2009
I have 2 production unit : A; B
They make payments to suppliers on certain periods : p1, p2..
The payments are on variable and different currencies : EUR, SEK, USD, CHF,AUD..
I explained the sample case table in attached excel sheet. My question is "how can i create the summary table easily and quickly by excel?"
currently I am calculating it by a simple excel formula (vlookup) that I need to change the range for each time. It s very time consuming and easy to make mistakes..
View 3 Replies
View Related
Dec 30, 2008
I have a large number of workbooks, each with one sheet. I need to do the following, and am too pressed for time to try and work it out from scratch, so am hoping to get some pointers on how best to set up the macro. All workbooks are in one folder.
From a Summary Worksheet:
Open Each Workbook
Go to Sheet1
Copy values from "a2", "c7", "e26", etc.
Paste as values in the Summary Worksheet on a new row.
Close Workbook.
Repeat until all Workbooks are extracted.
View 9 Replies
View Related
Jan 13, 2014
I had a data sheet that used about 20,000 rows, down to AF20000 or so. I deleted about 16,000 rows and now want to reduce the size of the worksheet so that when I hit Control+Shift+End, it takes me only to AF4000, not to AF20000. Can this be done without replicating into a new worksheet?
View 4 Replies
View Related
May 5, 2006
I have windows XP and xl2k. I have a single- sheet workbook (9MB) linked to another multi-sheet workbook of databases (20MB). The single-sheet workbook has about 100 pictures (30k ea) that, when a number is entered, searches the database. If no match is found for a given picture and product info, then the picture is deleted and the rows are hidden (macros). Usually, about 10 products are found in the database for any one entry.
My goal is that I can "save as > web page" and email these results off to my customer. The problem is the after-the-search result page is about 1MB and so too is the .htm file that is created when I "save as > web page." I was thinking that 10 pictures at 30k should be around 300k plus the formatting (no?). I cleared the "tools>option>general>web options>general>save any additional hidden data..." to little effect. Is there a macro that will delete the hidden rows which I should run AFTER the product search (because deleting them instead of hiding them messes up the macro run) - I'm sure that would help (right?). But my original thought was that the .htm file size would not be affected by hidden rows.
The per-item section of excel is 375 cells + the picture with simple font and formatting and no other objects, etc... When I delete the picture and save as > web page just this one-item section area (375 cells), the .html file is 37kb in size. That seems large to me (yes/no?). (10 x 37K = 370K before pictures!). This may be a reasonable file size, I'm no expert. I read a post where xl2003 has a compression application - is that what I need, or is there a 3rd-party app that can help me reduce the size of this file in htm. Or, is this the best I can expect from this method and I should be exploring elsewhere (metatags?).
View 5 Replies
View Related
Jan 12, 2007
I am working on a spreadsheet that pulls data from an external pivot table.
The spreadsheet is 25 columns wide by 72 rows. I have 15 worksheets currently but need to add another 7. The current file size is 22MB and I keep getting a NO MEMORY error message.
I am not using any fancy formulae just references to an external pivot table.
View 9 Replies
View Related
Aug 19, 2009
I am currently working on a very large spreadsheet with a current size of 94mb. So obviously I have problems running and using it.
My question is a general one, I need to understand better why a spreadsheet gets so large. It has 55 tabs with the largest having 1000 rows. It also links in a number of places between sheets but also to external locations. I have moved all files that it is linked to in to the same file.
I am at the point where I am thinking about starting again! And I was wanting some tips or even a recommendation of a book that could help me optimize my spreadsheet going forward so to increase the speed but also reduce the size.
View 20 Replies
View Related
Mar 12, 2009
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 9 Replies
View Related
Jan 10, 2010
I am trying to use the if function in an amortization table and I am wondering if there is a way to make it so if I cut the years in half if I can make the table read with 0's or dashes rather than to start counting back up again.
View 9 Replies
View Related
Apr 5, 2007
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 9 Replies
View Related
Sep 27, 2007
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 3 Replies
View Related
Nov 18, 2012
I need to create an amortization table for several thousand loans. I could create a seperate amortization schedule for each loan, but as you can imagine, doing so would take a long time.
These loans have varying interest rates, loan periods, and beginning balances, so it is not as if I could just add the beginning balances together and amortize on that basis.
It seems like this will require a VBA macro, however my programming skills are a little rusty.
View 3 Replies
View Related
Jun 11, 2014
I am in need of a formula to calculate monthly straight-line depreciation assets based on the current month’s days.
So, if an asset costs $10,000 and has 15 years of useful life, in June this would calculate 15 divided by 12 divided by 31 times 10,000. I want to put cost in one cell and date in another, for the formula.
BTW, someone is using this formula, =(16736*(1/15)*(6/12))*31/184
View 9 Replies
View Related
Feb 20, 2008
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 7 Replies
View Related
Jun 1, 2008
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 4 Replies
View Related
Apr 29, 2014
I have a spreadsheet that has amortization tables for several loans. I am trying to add a summary tab that will pull the next due date on each loan. Some of the loans are behind so I can't use a >today() function. I have been trying the following:
=MIN(IF(AND(ISBLANK(Smith!A:A),NOT(ISBLANK(Smith!B:B))),Smith!B:B))
Column A is date payment is actually made, and column B is due date, so I want to return the first column B that has nothing in column A.
View 1 Replies
View Related
May 23, 2013
I have a sheet (like example attached). it's broken out by month for many decades. certain columns like B-N need each field populated. There are others like O, P, Q, where I need to delete all cells except for 2 months that year (in this example, all except May and November).
Is there a shortcut method to do this quicker?
example.xlsx
View 3 Replies
View Related
Dec 2, 2008
This is a new payment status sheet that my company has put together to use with future clients. There are a few things that we would like fixed but can not figure out ourselves. I have attatched the document along with some notes as to what we can not get to work.
View 14 Replies
View Related
Apr 9, 2009
I would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month:
Select Creditor:
Enter Payment:
New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
View 14 Replies
View Related
Feb 8, 2010
Not sure if this is the correct section for this kind of query but I'd like some assistance with a calculation that I can't seem to figure out.
Essentially it's for calculating night payments for our employee time-sheets. Our staff have very sporadic shifts and are paid extra for working between the hours of 00:00 and 06:00, basically when employee's enter their start and end times I'd like the spreadsheet to automatically calculate how many hours they have worked between those hours, I imagine it's very simple but I cannot figure out which function to use.
To complicate matters, because staff can work shifts which start on one day and finish the next we work on a 48:00 clock basis so its' not only between the hours of 00:00 - 06:00 where they qualify for night payments but also from 24:00 - 30:00 if that makes sense?
View 9 Replies
View Related
Aug 12, 2013
I am trying to set up payment record sheet as follows
First Tab: This is the total value of each item, with the payment date in cell C1
Second Tab: Is the payment date of the item
Third Tab: Is the payment due tab
So what I want to do is enter the payment in C1 on the first tab. Then on the second tab I want to enter all the items that want paying on that day, but this will also have previous payment dates shown as a sort of record of what was paid on what day.
On the third tab I want it to show all the payments that correspond to the same date as C1 DATEVALUE
And on the gross tab I want to show all payments to date including the current date.
I have got the payment date to work but I cant get it to show all the previous values.
View 1 Replies
View Related
Aug 20, 2013
I'm currently developing a model for a payment schedule. For example, lets say a customer purchases an item from a store, but the store receives that payment in the following month. I'm looking to develop a dynamic model so that if I change the assumption from 1 month to another term (2,3,4+ months etc.) the model will adjust accordingly.
I've started with a formula
=IF(MONTH(H3)-MONTH(G3)=B14,G11*B13)
But of course this will not work once the term is set to larger than 1 month. With B14 representing 1 month, and G11*B13 being payment information (price * quantity).
View 2 Replies
View Related
May 2, 2014
I have a list of several hundred loan recipients who are all starting to pay their loans back on different dates. I am able to get the calculation to work so that I just need to put in the start date and excel works out the other 5 or 6 payment dates.The problem is, some people pay back on the 29th, 30th of the month which is giving me a headache when they are supposed to pay in February. The formula I am using calculates the next date of payment to be on the 3rd March (skipping the February payment all together).
The formula I am using is : =DATE(YEAR(A10),MONTH(A10)+1,DAY(A10))
View 3 Replies
View Related
Aug 21, 2008
I have a situation where I need to highlight different customers based on what there terms of payment are. Conditions 1 and 3 seem to be working fine but for some reason condition 2 is not working at all. I made sure all names are exactly as listed in the A2 cell....
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
Nov 3, 2006
I used erecord the other day to do my activity statement for the first time (I have just started a home based business which has not yet started trading but I had to send the BAS for the purchases that I had made for the business) and it was quite easy to use and you can send it electronically to the ATO which saves a lot of hassle particularly as I am very not accountancy litterate. However I am trying to develop an expenses/payment spreadsheet similar in function to erecord but that allows me to categorise the inputs.
My headers are:
Date
Cbookref = (drop down validation box) similar to a chart of account #
Category = ie - advertising accounting fees etc.. uses a look up function with cbook ref to populate field
Description .........................
View 5 Replies
View Related
May 21, 2012
I am working with an amortization table and need to work out the elapsed time to amortize the loan.
Currently the worksheet is working fine and calculates the end date of the loan but the length of the column varies depending on the scenario.
I need a formula to calculate the time that elapses between the first date and last date.
The data starts in cell b13 and the last date could appear in pretty much any cell below that, so the formula will need to look for the last valid entry.
I am using excel 2003.
View 5 Replies
View Related
Sep 30, 2013
I'm working on a report where i need to find the last date a payment was made by a certain client. The problem is that not all clients have the same amount of payments so I cant just choose a payment number in my formula.
Trishlast payment.xlsx
View 6 Replies
View Related
Mar 12, 2007
i have myself a table on the left is all the members names and along the top are 12 dates (1/1/07, 1/2/07 etc).
In another sheet i have a drop down box for the name of the person and a dropdown box to select a date and then a space to enter the amount paid so i need a macro that will find the name and date i selected from one sheet and take the amount paid into the other sheet and insert the amount in the correct place.
If it is unclear what i mean i will post screenshots tomorrow.
View 12 Replies
View Related
