Joining these formulas so they work?

[Scoober]

Hi - I am working on a mortgage spreadsheet.

I need the spreadsheet to work out interest only calculations, principle &
interest calculations, and remain empty if no loan amount or interest rate is
entered.

I currently use this formula that preforms step 1 and step 3 perfectly

O21: =IF(OR(L21="",N21=""),"",L21*N21)

Cell definition is L21 = loan amount, N21 = Interest rate

The cell that will have either IO (Interest Only) or PI (principle and
Interest) is I21.

The principle and interest formula I want to use (I know it's crude, but
does the job required) is =PMT(0.09/12,30*12,-L21)

To sum up:

When IO is placed in I21 I require the 'interest only' formula to work, when
PI is place in I21 I require the 'Principle and Interest' formula to work,
and if nothing is placed in L21 or N21 when nothing is placed in O21.

Any help would be appreciated.

Thank you in advance.

Cheers Scott

 

[Max]

One way:

In O21
=IF(AND(I21="IO",L21<>"",N21<>""),L21*N21,IF(AND(I21="PI",L21<>""),PMT(0.09/12,30*12,-L21),""))

Works? High-five it here then, click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

 

[Scoober]

Thanks Max,

Almost there! The Interest only formula works but the Principle and interest
formula is displaying a monthy total only when I,m after the total paid for a
year, so something is wrong with MY original formula!

Can you help?

Scott

 

[Max]

Welcome, but you didn't click YES? although I answered your query ..

Think its best to have other responders better versed to comment on your PMT
formula. If nobody jives in here, you could start a new thread specific on it.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

 

[Scoober]

Sorry fella, didn't click YES because your formula didn't fix the problem!

 

[Max]

Sorry fella, didn't click YES because your formula didn't fix the problem!
But, but I DID answer your original query, in all its pristine glory (look
closely at what you originally posted, inclusive of your subject line). Why
the stubborn stingyness with the high-five? I can assure you that doing so
will NOT prevent other responders from jumping in with their alternative
offers for you, if that's your main concern with crediting my earlier
response as an answer.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

 

[Fred Smith]

Your PMT formula returns a monthly payment because that's what you asked
for. You gave it a monthly interest rate (0.09/12), and a term in months
(30*12). What else would you expect from this other than a monthly payment?

If you want an annual payment, use annual figures, as in:
=PMT(0.09,30,-l21)

Now will you give Max his high-five?

Regards,
Fred.

 

[Scoober]

Sorry Max my fault, I thought by hitting yes it would close my enquiry.

Thank you for your help. Is two thumbs up better than a high-five?

 

[Max]

Sorry Max my fault, I thought by hitting yes it would close my enquiry.
No prob. At least the exchange provided a clarification useful to other
posters who regularly use MS' web interface

Thank you for your help. Is two thumbs up better than a high-five?

Welcome, no prob with the symbolics. Thanks for the ratings.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

 

[Fred Smith]

Max,

Now that we've solved Scoober's problem, I'm interested in what the ratings
do for you. Do they get you more status in a particular website, or do they
attract people to your website? Is this the same as 'Click Yes if this was
helpful' that I see on other people's posts?

Thanks for your time,
Fred.

 

[Max]

Hi Fred,

Is this the same as 'Click Yes if this was helpful' that I see on other
people's posts?

Yes, I believe so, for responders who use MS web interface to respond.

I'm interested in what the ratings do for you

Altruistic, really. Taking the cue from MS' newsgroups' web interface help,
ratings are important to build trust in communities, so that readers know
that they can count on the accuracy of the information they read in
discussion groups. If the post answered the question, it is important to
give the contributor credit for the answer, by rating that post as an
answer. And the progressive system awarded bronze-silver-gold icons based on
the ratings given does provide consistent contributers a measure of due
recognition (besides personal satisfaction) of their performance, in my
opinion. Ahhh, who knows? One fine day, MS may decide to reward contributers
for their fine, long service "achievements"/performance in helping others
with something more tangible (tongue-in-cheek).

Btw, I actually don't have a website. I do use savefile.com as a
freefilehost to trail along an easy, FOC, excel samples archives link for
any interested readers in my signature. I don't receive any monetary gains
from maintaining that archives "service".
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---