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

One way:

In O21:
=IF(AND(I21="IO",L21<"",N21<""),L21*N21,IF(AND(I 21="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" wrote:
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.

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" wrote:

One way:

In O21:
=IF(AND(I21="IO",L21<"",N21<""),L21*N21,IF(AND(I 21="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" wrote:
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.

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" wrote:
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

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



"Max" wrote:

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" wrote:
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

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
---

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" wrote in message
...
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" wrote:

One way:

In O21:
=IF(AND(I21="IO",L21<"",N21<""),L21*N21,IF(AND(I 21="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" wrote:
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.