I've been given a problem (but don't quite understand it)

[macandal]

Hello folks. It's not so much I can't do this problem, it's that I don't quite understand what they're saying.

This is the problem. I'm being asked to compare two mortgages. They're both for the same amount, split into two mortgages, but with different terms. Here's what they say:

1st option (the total being borrowed is $500K):
A first mortgage of $400K @ 4.5% amortized over 30 years, with a second mortgage of $100K @ 5%, interest only for 10 years, but with the intent of paying off with equal annual lump sum payments over 5 years.

I understand the first part; the part with the straight forward mortgage. $400K @ 4.5% for 30 years. Easy. Simple. Straight forward. But what about the second part? The part with the "interest-only" mortgage? Are they saying that this is also a 30-year mortgage, with the first 10 years being "interest-only", after which it turns into a mortgage like the $400K (where you pay principal+interest)? I think that's it. But what about the part that begins, "...but with the intent..." Are they saying that the second mortgage will be paid off in 5 years? Not 30, not 10, but 5? I'm confused. Can you guys please help?

The 2nd option (the other mortgage I'm comparing the 1st one to) is set up in a similar way. If I understand what I've quoted above, I will understand this one too. Thanks.

Oh, by the way, I'm being asked to compare the two options and decide which one is the better one.

Thanks.

 

[kirby]

An interest only mortgage - you pay interest only (and nothing to principal) for the term of the loan then you pay all the principal in one big ("balloon") payment at maturity. But in this problem they said pay the principal in equal annual lump sum payments over 5 years. So at end of year one pay $20,000 to principal, at end of year two pay $20,000 to principal and so on. so yes the second mortgage will be paid off in 5 years.

 

[macandal]

Thanks. So, is this what's happening on that second mortgage:

Yr 1: ($5,000x12)+$20,000=$80,000 (balance at end of Yr 1)
Yr 2: ($4,000x12)+$20,000=$60,000
Yr 3: ($3,000x12)+$20,000=$40,000
Yr 4: ($2,000x12)+$20,000=$20,000
Yr 5: ($1,000x12)+$20,000=$0​

Is that it? Did I get it right?

Thanks.

 

[macandal]

Sorry. My numbers are wrong. This is what's happening:

Yr 1: ($416.66x12 or $5,000)+$20,000=$80,000 (balance at end of Yr 1)
Yr 2: ($333.33x12 or $4,000)+$20,000=$60,000
Yr 3: ($250x12 or $3,000)+$20,000=$40,000
Yr 4: ($166.66x12 or $2,000)+$20,000=$20,000
Yr 5: ($83.33x12 or $1,000)+$20,000=$0

My apologies. I multiplied the annual interest amount by 12.

Now, is that it? Did I get it right this time?

Thanks.

 

[kirby]

Yes you are ok. Be sure to note that the amounts you have in parentheses represents interest while the 20 k payments are principal

 

[macandal]

Thanks. Ok. So, since this problem is about comparing two mortgages, how would I model the two options to compare in Excel? Is there anything in Excel to help me present these two mortgages? Thanks.

 

[kirby]

Yes now you use excel's irr function to get the internal rate of return on each project if the timing of the cash flows is irregular or use npv function if regular.