Mortgage Template

  • Thread starter Thread starter Michael Bartos
  • Start date Start date
M

Michael Bartos

I have made a mortgage template using the PMT function. Since the template
has the formula but no data in the cells, the cell with the formula returns
an #DIV/0! error message. I'd like to change that to NA or -.

According to the Excel help file, you wrap the function
=PMT($C$6/12,$C$7,-$C$5) in the ISERROR function, but I can't seem to get it
to work. Does anyone know how to do this?

Thanks.

Michael Bartos
 
Hi Michael!

I'm allergic to handling all errors and prefer handling the specific
problem. My suggestion would be:

=IF(COUNTA($C$5:$C$6)<3,NA(),PMT($C$6/12,$C$7,-$C$5))

The #DIV/0! arises because C5:C6 are empty.

=COUNTA(C5:C6)<3
Returns TRUE if any of those cells are empty.

The danger with using ISERROR is that the error may be caused by
something other than empty cells and you could get the NA returned and
shield that error.

A classic case might be where you are using the NPER function and
where the amount of the payment is less than the rate * loan. If you
use the ISERROR approach you will get a return of NA. If you use the
COUNTA approach NA is returned if one of the cells is empty but #NUM!
is returned if the payment is less than the rate * loan.

Also I prefer to use the NA() rather than "NA". It will follow through
better to subsequent formulas and will be caught by ISERROR functions
used later.

--
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.
 
Hi Michael!

Tip on the MS amortization schedules. They make heavy use of named
formulas.

I've got a few that you can have on direct email request. They're
fairly well annotated with the construction points.

--
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.
 
Dear Norman,

Thank you very much for your elegant solution. The only thing is that when I
filled in the data, the PMT function did not work. Without the data, I got
#N/A, but the loan, the rate and the length amounts, too, I still got #N/A.
I'm checking to see if I wrote it correctly.

Many thanks for your help.

Michael Bartos
 
Hi Michael!

It was working when I posted it.

--
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.
 
Back
Top