Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Yield Maintenance - Mortgage Prepayment Penalty

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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.




View Answers     

Similar Excel Video Tutorials

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
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
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
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
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

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
30-Year Term
10-Year Interest only period

After the 10th year, the numbers should run based on a 20-year 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
30-Year Term
10-Year Interest only period

After the 10th year, the numbers should run based on a 20-year 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 semi-annually, 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 semi-annually, 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),B1-B3,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 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 2012-13, 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 2013-14.



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 2012-13, 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 2013-14.

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 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.


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



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 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.



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.


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?



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


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 A-J.

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