Calculating a mortgage payment

[Erin Jenkins]

When I use the function =PMT(D2/12,C2*12,A2,0,0) it calculates the righ
payment but it shows as a negative number. How do I make it show as
positive number?

d2=8.15%
c2=30 years
a2=$8170

 

[Guest]

Multiply by -1. i.e:

=-1*PMT(D2/12,C2*12,A2,0,0)

 

[Paul]

Or even just
= -PMT(D2/12,C2*12,A2,0,0)

Multiply by -1. i.e:

=-1*PMT(D2/12,C2*12,A2,0,0)

 

[Erin Jenkins]

I hate it when it's so simple. That's typical of me though over
complicating projects.

Thanks

 

[Dave Peterson]

MS has a bunch of templates at:
http://office.microsoft.com/templates/default.aspx

(You may want to see if any useful to you.)

 

[Norman Harker]

Hi Erin!

It's a matter of taste often but I'd prefer to change my signs within
the function rather than just negate the entire answer. Simple cases
are no problem but life can get very difficult for inputs and
interpretation of outputs when you start dealing with more difficult
cases.

I'd prefer to get the sign to reflect the flow of the persona (either
borrower or lender)

This gives me for a lender:

If I have the A2 input as a positive:

=PMT(D2,C2*12,-A2,0,0)

If I have the input in A2 as a negative:

=PMT(D2,C2*12,A2,0,0)

This would indicate, correctly, a positive payment. Correctly? The
payment is coming towards me.

The reason for me wanting to be strict on the signage of flows is that
life can become more complicated with (for example) balloon mortgages
where there are terminal payments or even terminal receipts if part of
redemption is by another means.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.