
Yield Maintenance  Mortgage Prepayment Penalty


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
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.
Similar Excel Video Tutorials
VLOOKUP for Mortgage Calculator
 See how to use VLOOKUP to create a Mortgage Calculator that can handle 4 different types of loans: Begin Annuity, End Annuity, Interest Only and Other ...
Bond Quoted Price & Yield To Market
 Download Excel workbook http://people.highline.edu/mgirvin/ExcelIsFun.htm Learn how to take a quoted Bond Price and calculate the Yield To Market ...
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?
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!
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!
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?
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)
I need to create a If statement to calculate a Penalty using specific terms & Rates.
If my term is Less then 12 mths (Balance x .5%),
if my term is 12 to 23 mths (Balance x 1%),
if my term is 24  35 mths (Balance x 2%),
if my term is 36  47 mths (Balance x 3%).
Balance $ 1,000.00 Term 14 Penalty ???
We will enter the balance and the term but how do I write the if statement to look at the month ranges to calculate penalty?
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.
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
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!
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?
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
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.
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
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
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.
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 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?
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!
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!
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)
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.
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
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
I'm looking for a formula to help me determine how quickly I can pay down the remaining amount of my mortgage by making additional payments to the principal every two weeks.
Specifics:
Original mortgage amount  $195,000.00 (8/27/03)
Interest rate  4.875%
Length of term  20 years
Payment terms  Biweekly
Total # of payments  455
Biweekly payment  $636.74 (principle +interest)
# of payments to date  156
Current balance  $142,759.03
Question: How long will it take to pay off the full amount if an additional $100.00 ($200.00; $300.00; etc) is added to the principle every two weeks.
Ken Ellis

