Formula for mortgage payments with prepayment allowed?

I see a bunch of online calculators that allow you model your load payments given monthly prepayments etc, but none for what I want to model, which is weekly prepayments on my 2nd mortgage. So I am trying to find a formula or an algorithm that will calculate the missing variable given 5 out of these 6 variables:

present value pv

future value fv

periodic rate r

number of periods n

periodic payment pmt

periodic principal-only prepayment pre

For example,

  1. given pv = 100,000; fv = 0; r = 8%; n = 5; pmt = 1000, calculate pre = prepayment required per month. Or

  2. given pv = 100,000; r = 10%; n = 6; pmt = 500; pre = 500; calculate fv = remaining balance on the loan,

etc etc.

Any help? I am not looking for a link, I want to know the formula or algorithm I can use in each case.

PSA prepayment benchmark: 0.2% prepayment rate (CPR) on 30-year mortgage per month, increasing by 0.2% per month until month 30. After month 30, constant 6% prepayment rate for remainder of mortgage.

  1. Use PSA assumption to find relevant CPR.

  2. Find Single Monthly Mortality Rate (SMM): SMM = 1-(1-CPR)^(1/12)

  3. Monthly prepayment = SMM * (mortgage balance - monthly principal payment)

I hope this helps.

Please don’t think I am being rude, but your answer is not at all what I am looking for.

I am looking for a closed form or iterative solution to calculate one of

pv, fv, pmt, n, r, pre

given the other five. The financial calculator (e.g. TI BA-II) will let me calculate any one of pv, fv, n, r or pmt if I gave it other four; and I also know how to do it “by hand” (using a mathematical expression) in each case except r, for which I need trial-and-error.

I want something similar for the situation where prepayment is allowed without penalty.

I am probably not understanding your question due to the “required prepayment” you mention. What variables are changing on your mortgage? What exactly are you trying to solve if most of these are static? For example, are you trying to see what the NPV benefit of making periodic prepayments is?

If you build a amortization schedule where instead of having a constant payment, you have a new pmt on every line calculated based on the remaining number of periods (for example 360-whatever payment period) and the remaining loan balance, you can incorporate prepayments. Then there is the obvious interest component and everything else is principal, which you could separate however you wanted by just adding a second column. That way you could actually hardcode your actual vs scheduled payment and the difference would be your prepayment. Don’t know if that helps?