Loan Amortization Calculations
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
ADVERTISEMENT
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
Apr 30, 2009
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 7 Replies
View Related
Mar 15, 2007
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
View 5 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
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
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
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
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
Nov 30, 2009
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 11 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
Oct 3, 2013
1. Want to findout in which stage the loan is ? Eg: 1212321231 is in PROCESSING STAGE because the date appears under processing .
2. want to find out how many stages it has paased ? Eg: Loan 1212321231 has passed 3 stages and now in 4th stage(processing).
View 7 Replies
View Related
Apr 19, 2006
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 9 Replies
View Related
Jan 8, 2007
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 3 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
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 29, 2013
I have a file which student Monthly Loan Payment schedule.
I want to make a list which shows all payment period by installments and payment amount , dates by Student names.
Attached file, you can see all detail and wanted LIST.
View 5 Replies
View Related
Jan 31, 2014
I currently use goal seek to calculate an interest rate for a loan product. My problem is i would like to have the same function but not through a goal seek. In goal seek i have to set the value i want to achieve but ideally i want it to calculate automatically
I have attached a workbook with details. I use a loan amortization schedule to calculate the interest from parameters set on sheet 1
View 2 Replies
View Related
Aug 27, 2008
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 9 Replies
View Related
Aug 2, 2014
I have been loaning my brother money over the past 14 months. The loans have been in the form or $1000 per month plus random payments for one-off expenses like doctors fees. He's not paid anything back yet but we want to know what the total owed is for interest of 10% per annum.
I can easily create a table with payments I've made and the dates with a running total of how much I've paid but how to I create a running balance of what he owes over time based on adding in interest. This might end with a one-off payment in a couple of months, I'd like to calculate what is owed there as a minimum.
View 7 Replies
View Related
Aug 30, 2009
how I can solve the issue of creating a spreadsheet (similar to an amortization one) that could deal with unequal re-payment regime as well as unfixed (anytime of the month) payment periods.
View 9 Replies
View Related
Feb 21, 2010
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 9 Replies
View Related
Apr 9, 2014
I have a created a Data Chart Below. A - C are the columns and 1-7 are the rows. I have hard-coded the equation in cell B5 that I am using.
What I want to do is input an number into B3 that automatically makes Cell B5 equal to Cell C1. Is there a process in excel you can use to do this. Or do you just have to use trial and error?
A B C
1Loan Amount$10,500,000 0.0730041581143804
2Term 10 years
3Rate
4Amortization30 years
5Constant (K) =PMT(B3/12,(B4*12),-1000)*0.012
6Annual Payment$766,544
7Monthly Payment$63,879
Example is attached.
View 1 Replies
View Related
Jan 26, 2014
I have created a excel sheet here i want the total interest charged for three months in 3rd mnth interest charged column, if i select 7 mnths term total interest charged for 7 months should come in 7th month interest charged colum, if it is 13 months total interest for 12 months in 12th month interest column and remaining 1 month interest in 13th month interest charged column
INTEREST CAL.xlsx
View 1 Replies
View Related
Nov 10, 2013
With the data table given below, how can I formulate the yearly installment based on the tenure. Like below example.
Name
Loan
Yrly Pymnt
No. of Tenures
Y2014
Y2015
[Code] ........
View 3 Replies
View Related
Jun 12, 2014
Trying to automate the period part of the impt function
To calculate current value of loan i have the below formula below with the 3 being the current period
=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)
What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.
View 4 Replies
View Related
Jun 22, 2008
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 2 Replies
View Related
