In an attempt to better my skills at capital allocation, as an exercise I put together an Excel sheet that attempts to calculate the rate of return from an early payment to the principal of a mortgage. The numbers and situation do not apply to me and the example is strictly math based. The example I was trying to calculate is:

$100k, fixed 30 year, 4.25%

Person is able to put down $10k (10% of mortgage) at some point during the 30 years

Calculate the return of the early $10k payment

The excel sheet works fine at determining how much principal, interest, and when the loan will complete early. Due the amortization of the mortgage, the return depends on when the payment is made during the life of the loan. The trouble I am having is determining the return of the $10k that was paid to the principal early. I put together a few cases off to the right that try and determine the return. I dont think any are exactly correct.

Could anyone provide guidance on this calc?

Thanks!

EDIT: Is it just as simple as you save the 4.25% of the $10k every year? Should there also be a way to account for the shortened duration of the loan?