PMT function and 1st payment partway through a period

[Robbie G]

I am using the PMT function to calculate mortgage payments. payments are at
the end of each month and there will be 240 payments. how should i account
for the fact that the mortgage may start partway through the first month?

thanks.

 

[Joe User]

I am using the PMT function to calculate mortgage payments.
payments are at the end of each month and there will be 240
payments. how should i account for the fact that the mortgage
may start partway through the first month?

I cannot think of a way to use the PMT function for this. And my
mathematical formula is complicated. See below.

Alternatively, you can use Solver or Goal Seek.

Suppose B1 is the date of the loan, B2 is the loan amount, B3 is the annual
interest rate, B4 is the number of monthly payments, and B5 is the date of
the first payment (less than month after B1).

Assume that B6 will contain the monthly payment. And B7 contains the
following formula, assuming a US loan or similar:

=FV(B3/12, B4-1, B6, -B2*(1+(B5-B1)*B3/365) + B6)

With Solver or Goal Seek, set the target cell to B7, the target value to 0,
and the changing cell to B6. Execute Solver or Goal Seek.

The result in B6 is the "exact" payment.

Instead of using Solver or Goal Seek, you can use the following formula in
B6:

=B2*(1+(B5-B1)*B3/365) * (1+B3/12)^(B4-1)
/ ( ((1+B3/12)^(B4-1)-1)*12/B3 + (1+B3/12)^(B4-1) )

That is the Excel formulation of the following rewritten to solve for PMT:

0 = (PV*(1+(d2-d1)*i/365) - PMT) * (1+i/12)^(n-1)
- PMT*((1+i/12)^(n-1) - 1) / (i/12)

for PV>0 and PMT>0.

 

[joeu2004]

I am using the PMT function to calculate mortgage payments.
payments are at the end of each month and there will be 240
payments. how should i account for the fact that the mortgage
may start partway through the first month?

I cannot think of a way to use the PMT function for this. And my
mathematical formula is complicated. See below.

Alternatively, you can use Solver or Goal Seek.

Suppose B1 is the date of the loan, B2 is the loan amount, B3 is the annual
interest rate, B4 is the number of monthly payments, and B5 is the date of
the first payment (less than month after B1).

Assume that B6 will contain the monthly payment. And B7 contains the
following formula, assuming a US loan or similar:

=FV(B3/12, B4-1, B6, -B2*(1+(B5-B1)*B3/365) + B6)

With Solver or Goal Seek, set the target cell to B7, the target value to 0,
and the changing cell to B6. Execute Solver or Goal Seek.

The result in B6 is the "exact" payment.

Instead of using Solver or Goal Seek, you can use the following formula in
B6:

=B2*(1+(B5-B1)*B3/365) * (1+B3/12)^(B4-1)
/ ( ((1+B3/12)^(B4-1)-1)*12/B3 + (1+B3/12)^(B4-1) )

That is the Excel formulation of the following rewritten to solve for PMT:

0 = (PV*(1+(d2-d1)*i/365) - PMT) * (1+i/12)^(n-1)
- PMT*((1+i/12)^(n-1) - 1) / (i/12)

for PV>0 and PMT>0.


[Duplicate posting because it appears that postings to the MSNews server are
not copied to the MS Discussion Groups server sometimes.]

 

[Joe User]

PS....

=FV(B3/12, B4-1, B6, -B2*(1+(B5-B1)*B3/365) + B6)
[....]
=B2*(1+(B5-B1)*B3/365) * (1+B3/12)^(B4-1)
/ ( ((1+B3/12)^(B4-1)-1)*12/B3 + (1+B3/12)^(B4-1) )
[....]
0 = (PV*(1+(d2-d1)*i/365) - PMT) * (1+i/12)^(n-1)
- PMT*((1+i/12)^(n-1) - 1) / (i/12)

A lender might use a denominator of 360 instead of 365 for a fractional
monthly payment, following the rules for computing the APR according to
Appendix J of the US Truth In Lending Act (Reg Z).


----- original message -----

I am using the PMT function to calculate mortgage payments.
payments are at the end of each month and there will be 240
payments. how should i account for the fact that the mortgage
may start partway through the first month?

I cannot think of a way to use the PMT function for this. And my
mathematical formula is complicated. See below.

Alternatively, you can use Solver or Goal Seek.

Suppose B1 is the date of the loan, B2 is the loan amount, B3 is the annual
interest rate, B4 is the number of monthly payments, and B5 is the date of
the first payment (less than month after B1).

Assume that B6 will contain the monthly payment. And B7 contains the
following formula, assuming a US loan or similar:

=FV(B3/12, B4-1, B6, -B2*(1+(B5-B1)*B3/365) + B6)

With Solver or Goal Seek, set the target cell to B7, the target value to 0,
and the changing cell to B6. Execute Solver or Goal Seek.

The result in B6 is the "exact" payment.

Instead of using Solver or Goal Seek, you can use the following formula in
B6:

=B2*(1+(B5-B1)*B3/365) * (1+B3/12)^(B4-1)
/ ( ((1+B3/12)^(B4-1)-1)*12/B3 + (1+B3/12)^(B4-1) )

That is the Excel formulation of the following rewritten to solve for PMT:

0 = (PV*(1+(d2-d1)*i/365) - PMT) * (1+i/12)^(n-1)
- PMT*((1+i/12)^(n-1) - 1) / (i/12)

for PV>0 and PMT>0.


[Duplicate posting because it appears that postings to the MSNews server are
not copied to the MS Discussion Groups server sometimes.]