Thank you for your comment.

The values in cells S9 to S14 are the results of running the Solver function in Excel. The initial values can be arbitrary. If you were to set all those values to one, and then run Solver, each N-S-S-M formula in column F will be recalculated until the sum of each calculation is equal to the minimum amount. (You may have to run solver twice).

So, the answer to your question as to how I arrive at the values is that I did not arrive at the values, solver did.

Thanks again

Don

http://pistulka.com/Excel_Shared/Prepay_Penalty.xlsm ]]>

Tried using your 2015 Prepay tool but the VBA macro to calculate the breakeven rate is broken. Great tool, by the way.

Thanks ]]>

One more here asking for the password for this incredibly helpful model. Thanks so much for putting the time in and sharing this. ]]>

Thank you for the clear explanation and Excel example about the Svensson Model implementation.

Kind regards,

Eduardo ]]>

I am sending you a copy with your changes. ]]>

Thank you so much for the model! Could you please send me the password for the VBA code?

Thanks,

Marco

The formula is based on a normal mortgage of 12 monthly payments per year. If you change the months to 6 from 12, you need to adjust one of the other four inputs. For example, if you calculate a new payment based on the other data inputs in yellow, the formula will match the function. ]]>

When I plug your calculation for m (p*i/(12*(1-(1+(i/12))^-n))) into your mathematical formula for the CUMIPMT function in Excel, I get

=(((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^(b-1)+p*i/(12*(1-(1+(i/12))^-n))*q/i)-((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^e+p*i/(12*(1-(1+(i/12))^-n))*q/i))-p*i/(12*(1-(1+(i/12))^-n))*(e-b+1)

This seems to work fine as long as the Payments Per Year remains 12. If I change the Payments Per Year to 6 for example, the Excel CUMIPMT function does not equal what

=(((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^(b-1)+p*i/(12*(1-(1+(i/12))^-n))*q/i)-((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^e+p*i/(12*(1-(1+(i/12))^-n))*q/i))-p*i/(12*(1-(1+(i/12))^-n))*(e-b+1)

resolves to. How could I make this formula work for 6 Payments Per Year instead of 12?

Thank you!!

]]>