Yield Maintenance  Mortgage Prepayment Penalty 


Yield Maintenance  Mortgage Prepayment Penalty  Excel 
View Answers 
I am trying to calculate the prepayment penalty on a commercal mortgage  it
is based on yield maintenance:
Loan Amt: $13,600,000.
Term to Maturity: 10 Yrs.
Amortization Term: 29 Yrs.
Interest Rate: 6.85%
Term Remaining to Maturity: 2 Yrs. 3 Mos.
Equivalent Yield of RemainingTerm Treasury: 4.25%
I would greatly appreciate any suggestions.
is based on yield maintenance:
Loan Amt: $13,600,000.
Term to Maturity: 10 Yrs.
Amortization Term: 29 Yrs.
Interest Rate: 6.85%
Term Remaining to Maturity: 2 Yrs. 3 Mos.
Equivalent Yield of RemainingTerm Treasury: 4.25%
I would greatly appreciate any suggestions.
Helpful Excel Macros
Calculate the Future Value (FV) of Compound Interest in Excel  UDF Macro
 This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
 This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field  AutoFilter
 This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t
 This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t
Convert Numeric Dollar Values into Text in Excel  UDF
 Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
 Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Delete Entire Rows Based on Predefined Criteria (Text)
 This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
 This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Name Worksheets Based on Cell Contents
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Similar Topics
I'm trying to figure out what the yield to maturity is on a mortgage note. The tricky part (for me) comes in that the borrower wants to pay down the note early. Is there a way to figure out the new yield.
Rate without early payoff
PV = 35,250,000
Payment = 236,653.63
Nterms = 126 (1/month)
FV = 29,962,119.11.
Rate should be 7.090%
************************
So what would be the rate for the following:
PV = 35,250,000
Payment = 236,653.63
Nterms = 126 (1/month)
FV = 7,571,666.70
(There was a $15,000,000 paydown on term 60)
what is the rate yield of all cash flows?
Rate without early payoff
PV = 35,250,000
Payment = 236,653.63
Nterms = 126 (1/month)
FV = 29,962,119.11.
Rate should be 7.090%
************************
So what would be the rate for the following:
PV = 35,250,000
Payment = 236,653.63
Nterms = 126 (1/month)
FV = 7,571,666.70
(There was a $15,000,000 paydown on term 60)
what is the rate yield of all cash flows?
Hi everyone,
I am trying to modify my mortgage amortization spreadsheet, but I'd like to add an interest only function.
For example:
Loan amount: 100,000
Rate of 6%
Payment $500
30Year Term
10Year Interest only period
After the 10th year, the numbers should run based on a 20year amortization schedule.
Can anyone steer me in the right direction???
THANKS in advance!
I am trying to modify my mortgage amortization spreadsheet, but I'd like to add an interest only function.
For example:
Loan amount: 100,000
Rate of 6%
Payment $500
30Year Term
10Year Interest only period
After the 10th year, the numbers should run based on a 20year amortization schedule.
Can anyone steer me in the right direction???
THANKS in advance!
Hey everyone,
I've posted this question also to the board over at Mr. Excel:
http://www.mrexcel.com/forum/showthr...17#post1575117
I am trying to modify my mortgage amortization spreadsheet, but I'd like to add an interest only function.
For example:
Loan amount: 100,000
Rate of 6%
Payment $500
30Year Term
10Year Interest only period
After the 10th year, the numbers should run based on a 20year amortization schedule.
Can anyone steer me in the right direction???
THANKS in advance!
I've posted this question also to the board over at Mr. Excel:
http://www.mrexcel.com/forum/showthr...17#post1575117
I am trying to modify my mortgage amortization spreadsheet, but I'd like to add an interest only function.
For example:
Loan amount: 100,000
Rate of 6%
Payment $500
30Year Term
10Year Interest only period
After the 10th year, the numbers should run based on a 20year amortization schedule.
Can anyone steer me in the right direction???
THANKS in advance!
Hi everybody,
New member here. I know how to use the PMT function in Excel to calculate a mortgage payment based on loan amount, term, and interest rate. Is there a way to go backwards on that? In other words, to calculate a loan amount based on the desired payment, term, and interest rate?
New member here. I know how to use the PMT function in Excel to calculate a mortgage payment based on loan amount, term, and interest rate. Is there a way to go backwards on that? In other words, to calculate a loan amount based on the desired payment, term, and interest rate?
I would appreciate any help in finding a mortgage formula that would work. Here's the question I'm trying to solve within Excel. I lend out a mortgage of $180,932.50, compounding semiannually, 14 year amortization and on a 4 year term. I know that the total interest paid over the 4 year term will be $33,626 calculated semiannually, but what is the rate of interest? I'm not concerned with principle at this point. Anyone know a formula?
I am trying to calculate annual percentage rate for a mortgage estimate. The
Formula works as long as I don't use a rate above 5.5% and term in months
above 310. I need to calculate using 360 month term.
Also I need to combine 1st and 2nd lien loan info into a single APR. Anyone
know how to creat this;
Here is the formula I Have been using:
=RATE(B4,PMT(ROUND(B2/12,6),B4,B1,0,0),B1B3,0,0,0)*12
Data is as follows:
B1 = 110410 (Loan Amt)
B2 = 6.5% (Interest Rate)
B3 = 3360 (Estimated Closing Costs)
B4 = 360 (Term)
Formula works as long as I don't use a rate above 5.5% and term in months
above 310. I need to calculate using 360 month term.
Also I need to combine 1st and 2nd lien loan info into a single APR. Anyone
know how to creat this;
Here is the formula I Have been using:
=RATE(B4,PMT(ROUND(B2/12,6),B4,B1,0,0),B1B3,0,0,0)*12
Data is as follows:
B1 = 110410 (Loan Amt)
B2 = 6.5% (Interest Rate)
B3 = 3360 (Estimated Closing Costs)
B4 = 360 (Term)
I opened multiple Fixed Deposits/ Term Deposit on different dates in financial year 2012 13 for different terms. interest is compounded quarterly and paid on maturity. Is there a way I can calculate interest earned on all FD's for given financial year which ends on 31st March 2013.
Example
Fixed deposit #1:
Amount  Rs. 1,00,000
Rate  8.75%
Term  1 year 16 days
Date of opening  10 June 2012
Date of Maturity  26 June 2013
Fixed deposit #2:
Amount  Rs. 1,00,000
Rate  8.75%
Term  33 Months
Date of opening  16 August 2012
Date of Maturity  16 May 2015
Fixed deposit #3:
Amount  Rs. 1,00,000
Rate  8.75%
Term  28 Months
Date of opening  18 Oct 2012
Date of Maturity  18 Feb 2015
Fixed deposit #4:
Amount  Rs. 1,00,000
Rate  8.75%
Term  30 Months
Date of opening  07 Jan 2013
Date of Maturity  07 June 2015
Is there a formula in excel which will calculate compound interest for financial year 2012 13 based on dates of deposits?
As FD #1's first interest would be added on 10 Sept. second on 10 Dec, third on 10 March. So for financial year 201213, interest for 3 quarters should be accounted for even though the term of the deposit is 1 year and 16 days. Similarly for other FD's interest for whole quarter should be accounted for any given financial year plus remaining period if the FD is maturing in that financial year i.e 1 quarter and 16 days interest in financial year 201314.
I opened multiple Fixed Deposits/ Term Deposit on different dates in financial year 2012 13 for different terms. interest is compounded quarterly and paid on maturity. Is there a way I can calculate interest earned on all FD's for given financial year which ends on 31st March 2013.
Example
Fixed deposit #1:
Amount  Rs. 1,00,000
Rate  8.75%
Term  1 year 16 days
Date of opening  10 June 2012
Date of Maturity  26 June 2013
Fixed deposit #2:
Amount  Rs. 1,00,000
Rate  8.75%
Term  33 Months
Date of opening  16 August 2012
Date of Maturity  16 May 2015
Fixed deposit #3:
Amount  Rs. 1,00,000
Rate  8.75%
Term  28 Months
Date of opening  18 Oct 2012
Date of Maturity  18 Feb 2015
Fixed deposit #4:
Amount  Rs. 1,00,000
Rate  8.75%
Term  30 Months
Date of opening  07 Jan 2013
Date of Maturity  07 June 2015
Is there a formula in excel which will calculate compound interest for financial year 2012 13 based on dates of deposits?
As FD #1's first interest would be added on 10 Sept. second on 10 Dec, third on 10 March. So for financial year 201213, interest for 3 quarters should be accounted for even though the term of the deposit is 1 year and 16 days. Similarly for other FD's interest for whole quarter should be accounted for any given financial year plus remaining period if the FD is maturing in that financial year i.e 1 quarter and 16 days interest in financial year 201314.
P.S.. I have attached excel sheet with all the data.. If more information is required please let me know.
Friends,
I am trying to calculate yield rate for insurance policy in excel sheet. What I mean by yield rate is elaborated below;
Say I have taken an Insurance policy of x amount.
I am paying annual premium of say y amount, every year
I have paid annual premium for say 15 years
I get maturity amount after 15 years say z
I want to know what is the yield rate I got on the maturity amount for the amounts paid by me over 15 years.
I know that you might suggest a formula and I would rather appreciate, if you can provide me an excel template that would help me calculate yield rate, without making mistake in putting up formula.
I appreciate your time and attention.
With regards ::: Jack
I am trying to calculate yield rate for insurance policy in excel sheet. What I mean by yield rate is elaborated below;
Say I have taken an Insurance policy of x amount.
I am paying annual premium of say y amount, every year
I have paid annual premium for say 15 years
I get maturity amount after 15 years say z
I want to know what is the yield rate I got on the maturity amount for the amounts paid by me over 15 years.
I know that you might suggest a formula and I would rather appreciate, if you can provide me an excel template that would help me calculate yield rate, without making mistake in putting up formula.
I appreciate your time and attention.
With regards ::: Jack
I want to calculate the mortgage principle on a loan based on a given interest rate, term, and monthly payment. I know how to calculate the opposite using PMT. Is there one function to calculate the origonal loan principle? I think I could use FV  IPMT but that seems clumsy.
I usally just derive the mortgage formulas myself since I have a hard time understaning the excel functions.
I usally just derive the mortgage formulas myself since I have a hard time understaning the excel functions.
Hi guys,
Not a VBA question persay, but I have a few preferreds that I am setting up in my portfolio. The par is $25, and not $100 as the yield/price functions assume in Excel. Am I going to get the right yield if I simply amplify the par value, ie. multiple price by 4 and the redemption value by 4 as well? Similarly would I get the same yield if I divide the whole equation by 4?
Also I am calculating their YTM(Yield to Maturity) and YTC(Yield to Call) both using the Yield function. The only difference is I am assuming the call date is the maturity. (For my purposes I am fine with this calc)..Anyway I am getting the same damn yield on both of them..what I am doing wrong?
Sorry, not sure how to treat these suckers,..its funny though they were just having this debate @ work, are preferred stocks debt or equity?!?!  Low price movements following IRates+fixed dividends(albeit declared)..
Appreciate the help gentlemen!
Not a VBA question persay, but I have a few preferreds that I am setting up in my portfolio. The par is $25, and not $100 as the yield/price functions assume in Excel. Am I going to get the right yield if I simply amplify the par value, ie. multiple price by 4 and the redemption value by 4 as well? Similarly would I get the same yield if I divide the whole equation by 4?
Also I am calculating their YTM(Yield to Maturity) and YTC(Yield to Call) both using the Yield function. The only difference is I am assuming the call date is the maturity. (For my purposes I am fine with this calc)..Anyway I am getting the same damn yield on both of them..what I am doing wrong?
Sorry, not sure how to treat these suckers,..its funny though they were just having this debate @ work, are preferred stocks debt or equity?!?!  Low price movements following IRates+fixed dividends(albeit declared)..
Appreciate the help gentlemen!
I am trying to model out a yield scenarion and could use some help.
If I purchased a mortgage from a wall street firm for $175,500 which is generating monthly payments of 1,596.29 for 36 months. At that time the loan pays off at $200,000. Does anyone know how to model the scenario in Excel to calculate the yield on the investment of the 175,500?
If I purchased a mortgage from a wall street firm for $175,500 which is generating monthly payments of 1,596.29 for 36 months. At that time the loan pays off at $200,000. Does anyone know how to model the scenario in Excel to calculate the yield on the investment of the 175,500?
Hello,
I would like to calculate the interest i would pay on a loan and the capital
I would pay off each month.
Mortgage = 50,000
Term 25 years = 300 months
at a rate of 4.39% how much interest would I be paying and how much capital.
I could then work out how much quicker I could pay off the mortgage by
overpaying.
Matt
Does anyone know how to build an amortization schedule for a credit card.
For example
Say I have a mortgage loan, I can build an amortization schedule with:
loan amount
term
interest rate
Now I want to figure out when my credit card will be paid off
example:
Balance = 17,000
Rate = 14%
**** This is where I am confused  I don't have the term, because I want to find out when it will be paid off by making miminum payment)
or use a constant amount = 350 per month
thanks for any help
Josh
For example
Say I have a mortgage loan, I can build an amortization schedule with:
loan amount
term
interest rate
Now I want to figure out when my credit card will be paid off
example:
Balance = 17,000
Rate = 14%
**** This is where I am confused  I don't have the term, because I want to find out when it will be paid off by making miminum payment)
or use a constant amount = 350 per month
thanks for any help
Josh
I am trying to calculate an APR based on a mortgage. The formula that I have
input is as follows:
=RATE(A4,((PMT(A2,A4,A1,0,0))),A1+A3,0,0,0)*12
My Data is as follows:
A1 = 150,000.00 (loan amount)
A2 = 5.500 (Interest Rate)
A3 = 3000 (Closing Costs / Fees)
A4 = 360 (Term in months)
As far as I can tell I am entering all of the data in the correct format and
order but continue to receive a #NUM! error. Can anyone tell me what I am
missing or doing wrong. Thanks.
input is as follows:
=RATE(A4,((PMT(A2,A4,A1,0,0))),A1+A3,0,0,0)*12
My Data is as follows:
A1 = 150,000.00 (loan amount)
A2 = 5.500 (Interest Rate)
A3 = 3000 (Closing Costs / Fees)
A4 = 360 (Term in months)
As far as I can tell I am entering all of the data in the correct format and
order but continue to receive a #NUM! error. Can anyone tell me what I am
missing or doing wrong. Thanks.
I am looking for a couple of formulas, if anyone can so kindly lead me in the right direction.
I am trying to build a real generic credit card analysis calculator
1. Say I know these things
Bal. = 20,000
Rate = 14%
Min Payment = 4% of balance
How can I figure out how much interest they will pay over the life of the loan.
For example, they owe 20,000, plus interest of say something like 25,000, so overall they owe 45,000. I want to calculate the overall total payment.
2. I know how to use a mortgage loan amortization schedule as well as use the formulas.
But I am trying to derive how long it will take to pay off a credit card using the above information.
The difficulty I am having is that, I don't think of credit cards as having a term (e.g. 30 year or 360 months, like a mortgage)
I would be very appreciate for any help
Thank you,
Josh
I am trying to build a real generic credit card analysis calculator
1. Say I know these things
Bal. = 20,000
Rate = 14%
Min Payment = 4% of balance
How can I figure out how much interest they will pay over the life of the loan.
For example, they owe 20,000, plus interest of say something like 25,000, so overall they owe 45,000. I want to calculate the overall total payment.
2. I know how to use a mortgage loan amortization schedule as well as use the formulas.
But I am trying to derive how long it will take to pay off a credit card using the above information.
The difficulty I am having is that, I don't think of credit cards as having a term (e.g. 30 year or 360 months, like a mortgage)
I would be very appreciate for any help
Thank you,
Josh
Does anyone know how to computer yield to maturity, in Excel, for a bond that pays interest more than 4 times per year. Excel's builtin function limits payment frequency to 4 times per year. However, many bonds pay interest monthly.
I am trying to do some bond analysis on a spreadsheet. There are functions
for Yield & Yield to Maturity but none for Yield to Call. Does anyone know
how to accomplish thi in Excel?
for Yield & Yield to Maturity but none for Yield to Call. Does anyone know
how to accomplish thi in Excel?
Hello,
I am working with an Excel list of contacts for our company.
Each row has the persons first name, last name, original mortgage amount, origination
date, loan term (months or years) interest rate, etc...
I am trying to calculate the mortgage "pay off" for each of these records. That is, I am trying to figure out the principle balance due on the loan as of today.
I understand that I can do a loan amoritization using an Excel function.
Is there anyway of telling excel that I need the Principle balance on a particular month?
For example. Let's say that the borrower orginated a loan on 1/1/2008 for $100,000
at 5% for 30 years. I know that as of today, the borrower is approximately 39 months along into this loan. Is there a method for formula that I can use to calculate the Principle payoff as of today for this person? Am I able to tap into and use an Amortization function? I need to do this calculation for each record.
Thank you for your help.
Mike
I am working with an Excel list of contacts for our company.
Each row has the persons first name, last name, original mortgage amount, origination
date, loan term (months or years) interest rate, etc...
I am trying to calculate the mortgage "pay off" for each of these records. That is, I am trying to figure out the principle balance due on the loan as of today.
I understand that I can do a loan amoritization using an Excel function.
Is there anyway of telling excel that I need the Principle balance on a particular month?
For example. Let's say that the borrower orginated a loan on 1/1/2008 for $100,000
at 5% for 30 years. I know that as of today, the borrower is approximately 39 months along into this loan. Is there a method for formula that I can use to calculate the Principle payoff as of today for this person? Am I able to tap into and use an Amortization function? I need to do this calculation for each record.
Thank you for your help.
Mike
Hi,
I need help in constructing a mortgage loan formula whereby the Payment is known, the Interest Rate is known, the Term (# of years) is known, but the Principal Amount of the loan is unknown.
To better illustrate my question, I will first calculate the monthly payment for a $100,000 mortgage loan for 30 years at an interest rate of 6 per cent:
cell A1= 6% (the interest rate)
cell A2= 30 (the number of years)
cell A3= $100,000 (the principal amount of the loan)
The answer in cell A4 = $599.55
I got that result ($599.55) by typing the following formula into cell A4:
=PMT(A1/12,A2*12,A3)
My question is this:
What is the formula I can type in to find out what the Principal Amount of the loan would be if I already have the interest rate (A1), the number of years (A2), and the monthly payment (A4)?
I'm stuck on this. Any help would be greatly appreciated!
I need help in constructing a mortgage loan formula whereby the Payment is known, the Interest Rate is known, the Term (# of years) is known, but the Principal Amount of the loan is unknown.
To better illustrate my question, I will first calculate the monthly payment for a $100,000 mortgage loan for 30 years at an interest rate of 6 per cent:
cell A1= 6% (the interest rate)
cell A2= 30 (the number of years)
cell A3= $100,000 (the principal amount of the loan)
The answer in cell A4 = $599.55
I got that result ($599.55) by typing the following formula into cell A4:
=PMT(A1/12,A2*12,A3)
My question is this:
What is the formula I can type in to find out what the Principal Amount of the loan would be if I already have the interest rate (A1), the number of years (A2), and the monthly payment (A4)?
I'm stuck on this. Any help would be greatly appreciated!
Hi,
I need help in constructing a mortgage loan formula whereby the Payment is known, the Interest Rate is known, the Term (# of years) is known, but the Principal Amount of the loan is unknown.
To better illustrate my question, I will first calculate the monthly payment for a $100,000 mortgage loan for 30 years at an interest rate of 6 per cent:
cell A1= 6% (the interest rate)
cell A2= 30 (the number of years)
cell A3= $100,000 (the principal amount of the loan)
The answer in cell A4 = $599.55
I got that result ($599.55) by typing the following formula into cell A4:
=PMT(A1/12,A2*12,A3)
My question is this:
What is the formula I can type in to find out what the Principal Amount of the loan would be if I already have the interest rate (A1), the number of years (A2), and the monthly payment (A4)?
I'm stuck on this. Any help would be greatly appreciated!
I need help in constructing a mortgage loan formula whereby the Payment is known, the Interest Rate is known, the Term (# of years) is known, but the Principal Amount of the loan is unknown.
To better illustrate my question, I will first calculate the monthly payment for a $100,000 mortgage loan for 30 years at an interest rate of 6 per cent:
cell A1= 6% (the interest rate)
cell A2= 30 (the number of years)
cell A3= $100,000 (the principal amount of the loan)
The answer in cell A4 = $599.55
I got that result ($599.55) by typing the following formula into cell A4:
=PMT(A1/12,A2*12,A3)
My question is this:
What is the formula I can type in to find out what the Principal Amount of the loan would be if I already have the interest rate (A1), the number of years (A2), and the monthly payment (A4)?
I'm stuck on this. Any help would be greatly appreciated!
Can anyone help with finding or combining formulas to calculate the "yield to maturity" at purchase date for a security investment that pays principal and interest on a monthly basis?
For example:
Par/original face = 10,000,000
Factor = 0.98919653000
Current face = 9,891,965.30
Principal = 9,842,505.47
Purchased accrued interest = 28,714.18
Coupon = 5.75%
price = 99.5
PSA = 222
Weighted Average Life = 4.22 yrs
Other info if needed:
Settlement date = 1/20/07
Next pmt date = 2/15/07 (for accrual period of 1/1/07  1/31/07 OR a 14 day delay)
For example:
Par/original face = 10,000,000
Factor = 0.98919653000
Current face = 9,891,965.30
Principal = 9,842,505.47
Purchased accrued interest = 28,714.18
Coupon = 5.75%
price = 99.5
PSA = 222
Weighted Average Life = 4.22 yrs
Other info if needed:
Settlement date = 1/20/07
Next pmt date = 2/15/07 (for accrual period of 1/1/07  1/31/07 OR a 14 day delay)
Hello guys,
I got a question regarding some financial maths:
I want to write a function in VBA that calculates the following:
C: coupon
F: face value
P: price
y: Yield to maturity
P = C/(1+y)^1 + C/(1+y)^2 + C/(1+y)^3 + ... + C/(1+y)^n + F/(1+y)^n
Now let's assume C, F and P are given, I want to obtain y with a VBA tool, what's my play?
I dont want to use regular Excel.
Thanks in advance.
I got a question regarding some financial maths:
I want to write a function in VBA that calculates the following:
C: coupon
F: face value
P: price
y: Yield to maturity
P = C/(1+y)^1 + C/(1+y)^2 + C/(1+y)^3 + ... + C/(1+y)^n + F/(1+y)^n
Now let's assume C, F and P are given, I want to obtain y with a VBA tool, what's my play?
I dont want to use regular Excel.
Thanks in advance.
Hi there,
I'm new to Macros and have been looking around on the board and google for a while to try to do this myself, but haven't succeded and hope someone out there can help me.
Here's what i have and what i want to do:
I have, lets say 1000 individual mortgage loans with each loans characteristics in its own row. For example, the first loan is in row 1 with all of its mortgage characteristics (age, rate, term, balance, etc...) in columns A thru M. So loan two starts on row 2 and so forth.
I then have produced the amortization schedule based on this loans characteristics. The amortization table is on another sheet and takes up columns AJ.
What i want to do is create a macro that goes through and amortizes all 1000 mortgage loans and sums up certain columns in my amortization table and puts this data in its own sheet within the same workbook.
I can do this manually by amortizing each loan separately and adding the columns i want, but as you can imagine, it would take a very long time.
Thanks for your help ahead of time.
Darius
I'm new to Macros and have been looking around on the board and google for a while to try to do this myself, but haven't succeded and hope someone out there can help me.
Here's what i have and what i want to do:
I have, lets say 1000 individual mortgage loans with each loans characteristics in its own row. For example, the first loan is in row 1 with all of its mortgage characteristics (age, rate, term, balance, etc...) in columns A thru M. So loan two starts on row 2 and so forth.
I then have produced the amortization schedule based on this loans characteristics. The amortization table is on another sheet and takes up columns AJ.
What i want to do is create a macro that goes through and amortizes all 1000 mortgage loans and sums up certain columns in my amortization table and puts this data in its own sheet within the same workbook.
I can do this manually by amortizing each loan separately and adding the columns i want, but as you can imagine, it would take a very long time.
Thanks for your help ahead of time.
Darius
Hope you can help  I need to set up a loan ammortization schedule but not using a template.
This is my question that I need to answer using excel:
A fixed rate mortgage loan for $200,00 with a mortgage rate of 6.5% compounded monthly to be amortised over 20 years.
a) what is the mortgage balance at the end of year 1 (beginning of year 2)
b) what portion in dollars of the monthly mortgage payment for month 12 represents interest
I don't know what function arguments to use to find  interest paid for each period i.e period 1 to 12, principal paid for the periods and a ending balance for each period.
How do you set it up. Any help would be greatly appreciated
This is my question that I need to answer using excel:
A fixed rate mortgage loan for $200,00 with a mortgage rate of 6.5% compounded monthly to be amortised over 20 years.
a) what is the mortgage balance at the end of year 1 (beginning of year 2)
b) what portion in dollars of the monthly mortgage payment for month 12 represents interest
I don't know what function arguments to use to find  interest paid for each period i.e period 1 to 12, principal paid for the periods and a ending balance for each period.
How do you set it up. Any help would be greatly appreciated