Yield Maintenance - Mortgage Prepayment Penalty

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.

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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


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


Hi all,
The yield function in excel does not give the right value if I play around with the redemption value(% of par). I have checked this with other available online bond yield calculators such as

If I change the redemption value to 50 and the bond price to $50, ideally the coupon rate and the yield should be equal for any time period. While this happens in the calculator mentioned above, Excel gives me a different number. Is this actually a bug or am I missing the point?

Test this in Excel...


Also test different redemption and price values and compare them with the calculator above.

I am a mortgage consultant and I am trying to create a formula that will calculate APR with PMI. There is a catch though. The PMI will drop off in the middle of the loan. I would imagine it can be done using the RATE function, but I don't know how. Below is the a sample scinerio.

A1 Loan Amount: $250,000
B1 Closing fees: $5,000
C1 Rate: 6.25%
D1 Term: 360 months
E1 Payment: $1,539.29 (w/o PMI)
F1 PMI: $108

I can calculate APR that does not take PMI into consideration accurately (enough) using:

However, I don't know how to calculate the PMI into the formula. The PMI will cease to be necessary at 80% of the original loan value (A1) thus changing the monthly payment from $1,646.29 to $1,539.29

Any ideas or suggestions?


Have modified an amortization (for a loan i have given to somebody) to allow
me to modify and put in actual payments received. I need to include the
actual dates of payment as most are not received when they are due I need to
calculate late payment fees and penalty interest on overdue amounts. If I
just make a seperate worksheet and enter the values for late interest and
fees on both sheets manually, then I have to calculate the late interest
myself which I cannot do. To add more confusion, I really need to calculate
interest on a daily basis so I can charge late interest based on the number
of days overdue and compounding each day. I am no expert when it comes to
devising formula solutions, but I am pretty good at copying and pasting (yes
that was an attempt at humour)

Hi All! I am a new guy to the block and would appreciate some help concerning the following:

I need to calculate the proceeds of recurring payments at a fixed interest rate over a fixed term with one exception to the norm: Annually, the PAYMENTS grow bigger at a fixed rate (10% in the example).

It's like this:

Year 1: Payment: $1,200, interest 10%, term remaining: 20years.
Year2: Payment: $1,320 ($1,200 + 10%), interest still 10%, term remaining: 19 years of the initial 20.

The person will therefore be earning 10% compound interest on $1,200 for a full 20 years, 10% compound interest on $1,320 for 19 years, etc, until, in the last year, the payment will be $7,339.09. The person will earn 10% interest on that for 1 year only. What will the total payout be?

There has to be an easier way to calculate this than doing it the way I did here and hiding the calculations!

Hope to hear from you soon!


Does anyone know of an ARM Mortgage calculator in Excel that calculates an
amortization schedule based on interest rate assumptions?

Hi all,

this may be a math question, but I hope someone can help: When I use =YIELD I get a different result compared to =XIRR used on the modeled cashflows. When there are more payments per year, IRR goes up and Yield goes down.

Is this, because Yield assumes a zero return (i.e. no re-investing) of the intrayear payments?


Does anyone know a way to model a portfolio of performing loans made for auto finance? It's easy enough to model repayments for a single loan using NPV but are there any conventions for modelling multiple loans, the number of which changes from month to month owing to new loans, defaults, early payouts and going full term. By way of stabilising some variables, it's all right to assume, for example:

1. average loan size of $20k
2. interest rate 10%
3. term 60 months
4. balloon/residual $5k
5. average early repayment term 32 months

Or does anyone know a good reference that deals with modelling structured finance including securitization?


I input the following information for a standard semi-annual 30/360 bond and calculated the Yield. I then tried to replicate the Yield using a string of cash flows and XIRR, but the numbers differed. I've also experimented with converting one or the other from semi-annual to annual or vis-a-versa and that doesn't seem to solve the problem. It's also possible that I've miscalculated the cash flows or accrued interest. Could someone please help explain how these calculations differ and how to align them? Thank you.

Par 100.00 Price 73.00 Yield 13.128% XIRR 13.544% Coupon 5.875% Frequency 2.00 Issue Date 08/13/04 First Pay 02/13/05 02/15/09 2.94 05/05/09 - 05/08/09 (73.00) 05/08/09 (1.35) 08/15/09 2.94 02/15/10 2.94 08/15/10 2.94 02/15/11 2.94 08/15/11 2.94 02/15/12 2.94 08/15/12 2.94 02/15/13 2.94 08/15/13 2.94 02/15/14 2.94 08/15/14 102.94

Hi there,

I'm having a few problems in preparing a loan amortisation schedule without using a goal seek function that will calculate principal and interest, where the interest is calculated on an Actual/360 day basis and the principal and interest payment each month is kept reasonable constant (it can move slightly depending on the numbers of days in the month but I'm trying if possible to keep it constant).

I've seen some information which uses the PMT formula for calculating monthly payments but this takes the interest rate and does a more simply division by 12 - unfortunately when I use this function and calculate interest using the A/360 days the balance at the end of the loan term doesn't equal zero.

Can you please help!!



Does Excel contain a simple interest loan calculation function built in? Or do I need to specify how exactly simple interest calculates on a per diem basis? What I am trying to do is calculate finance charge based on daily interest such as a standard auto loan contract. I am supplying the interest rate, term, and days to first payment - which in itself contains all of the necessary data for calculation (assuming excel has the loan calculation built in), however simple interest is based daily, so the calculation needs to understand days in each month, and totals days across a long period of time, and I seem to remember excel having basic loan calculation features built in, however general excel help only seems to have basic functions. What would be the best way to approach this?

The Calculation of simple yield:

The Return*
__________ X 100= ROI (%)


so for example: I have 1000 bets with odds 2,00
500 of them are winning and 500 losing. So i am on 0 and my ROI is zero

Would like to get odds to have 15% yield,
How to calculate on what odds should i be to have ROI 15% ?
Because odds are not always 2,00 - they are different but i gave 2,00 as example...

Looks like very easy but can't figure it out!?

Hello Friends:

Please help me with a formula that can tell me how much interest rate (in percentage) I paid on this loan where I have the total interest paid over all monthly payments, original loan amount, and number of monthly payments made. At the end of the term of 360, all interest and principal was paid off.

Please see attached.

I work for an investment management firm and I am having trouble calculating municipal bond yields with Excel. My problem is two fold.

The first has to do with the how you write the formula or input the data. My traders use the following format:

yield(trade price, redemption price, rate, 2, settlement date, redemption date, "MUNI")

If you use insert function in Excel, the format is:

yield(settlement date, redemption date, rate, trade price, redemption price, 2)

If one of my traders sends me a spreadsheet using the first format/formula, it will properly calculate on my PC. But if I try to edit the formula in any way or replicate it on my own, Excel returns #NUM! We all appear to be using the same add-ins (I have the bond add-in installed).

The other problem is that if I use the second format, the one built into Excel, it returns the wrong yield. The first format of course returns the correct yield. How do I know it is wrong? If I check the yield using a financial calculator or a Bloomberg terminal it matches what the first formula returns.

Any help would be very much appreciated!!!

I need to caclulate an amount due to my client where there is an increase in
the amount owed each month (amounts vary) and an interest rate that is tied
to the prime rate. No payment to be made until the end of the term.

I have a time period everyday from 23:00 - 0400 where an extra shift penalty applies.

I have 8-12 hour shifts that commence before 23:00 and after 23:00.

Is there a way to calculate the number penalty hours that are worked during a shift.

I'd be very grateful for any assistance!


I'm trying to automate our payroll to calculate the number of hours worked to factor in shift times & penalty rates. At present, I have worked out how to calculate the number of hours worked with penalties but I don't know how get excel to recognise shift times.

I'm using the following formulas to work out;
1. Total hours worked (less unpaid breaks): =(D4-C4+(D4<C4))*24-0.9
2. Standard hours: =IF(E4>=7.6,7.6,E4). This is for a normal full working day (7.6hrs)
3. Time & a half penalty rate (first 2 hours): =IF(E4>=9.6,2,E4-F4)
4. Double time penalty rate (each hour thereafter the first 2 hours): =IF(E4>=15,0,E4-F4-L4)

These formulas work well but it won't work when I try & add shift time parameters. The shift times are worked out on the following time periods:
1. Morning shift: Shifts that finish between 12:30 & 14:30
2. Early afternoon shift: Shifts that finish between 19:00 & 21:00
3. Afternoon shift: Shifts that finish between 21:00 & 23:00
4. Night shift: Shifts that finish between 23:00 & 07:00

These shifts attract penalty rates also so I need a way to allocate the time to the correct cell. The actual $$ value calculations are done elswhere on the sheet.

I've tried a number of different ways to overcome it but I don't have a lot of experience with excel & only know how to use basic formulas. Any assistance would be greatly appreciated>



I have the following data:

Lease term = 34 months
Term = no payment for first 4 months then 0 monthly payments
Monthly payment = $3,600.00
Present value = $72,000.00

What is the formula?

Much Appreciation!!!!

Is there a way to calculate yield and duration on asset paying monthly. Currently excel allow only calculation on quarterly paying assets.

Does anyone know of any good spreadsheets for bond portfolio analysis? Also, I am in the process of creating a bond ladder spreadsheet and am hoping someone may have some insight.

As of right now I have all of the bond positions on my sheet weighted by total cost. I also have my portfolio broken down for each maturity, ie. 2010, 2011, and so on. I then have the average yield to worst for each breakdown. I am trying to get a sense of reinvestment risk and what should be optimal breakdown.

Any ideas/suggestions would be greatly appreciated. Thanks!

I am trying to enter a formula to calculate the maximum principal a company can borrow. I have the following data:
Maximum annual comittment to principal and interest - $1,704,752
Term of Loan - 5 years
Annual Interest Rate - 3.58%
Annual payments

I tried the PPMT function but am not getting the right answer?
I need this for a meeting tonight - appreciate any help I can get. Thanks.