How do I include PMT functions within an IF function

[Guest]

Trouble with Project 5, challenge 5 within Essentials workbook MS Office
Excel 2003, the problem:

10 homes categorized as either Model or Spec.

Create a worksheet to show monthly payment for each home-each with a
different selling price. I don't want to enter 10 separate PMT functions.
Instead I plan to enter one IF function to compute the monthly payment on the
first home, and then copy that function to compute the montly payments on the
other homes. If I include PMT functions for the Value_if_True and
Value_if_False arguments in the IF function, the strategy can work. But
HOW?! I've tried all sorts of arguments. Is this supposed to be a Nested IF
function, should I key in PMT within the Value_if_True argument somehow?

My test condition will be
d10=$b5$ D10=Model B5=Model

6.00%=$C$5
15=$d$5
12=$e$3
180,000=C10

12 payments per year
Type Home Annual Rate Term (years)
Model 6.00% 15
Spec 5.50% 30
-------------------------------------------------------------------------------
Home Model Selling Price Type Monthly Payment
#1 Ibis 180,000 Model ?????????
#2 Spoonbill 195,200 Spec ?????????

Hope this is enough info!
Thanks!

 

[NHarkawat]

If I understood your question correctly this could be a possible solution:

1)Create a 2 x 2 matrix as follows:
Rate Term
model 6% 15
Spec 5.50% 30


2) Select this 2x2 matrix and then go to Insert->Name->Create (check Top row
left column)
To make sure that you created the correct labels type in any empty cell the
following:
=model rate
and you should get 6% as your answer

3) Use this formula and copy it across
=PMT(INDIRECT(D7) Rate/12,INDIRECT(D7) Term*12,C7)
where D7 contains "Model" or "Spec"
C7 contains the Selling Price

Hope this helps