APR with PMI

[jermdross]

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:
=RATE(D1,-E1,A1-B1)*12

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?

Thanks

 

[bs0d]

add an if condition to change the formula once 80% is reached.

Do a yearly breakout and show the change.

Sounds like you know your answer, but want to see it in Excel?

 

[jermdross]

I considered using an IF, but I can't seem to make that work either. You are correct though, I know the APR I just need to find a formula to calculate it in Excel.

Thanks