Amortization Schedule

[bw60120]

I am doing an amortization table and was wondering if you guys could
help me out. The amortization table I am making should allow for a
possible 360 periods. However, when a smaller number of periods are
required, say 60 for a 5 year loan with monthly payments, all rows below the last significant row must be blank (no negative or extremely large numbers). I think it involves conditional formatting or the IF function, but I can't remember how to use those exactly. If you could help me out, I would really appreciate it. Thanks for your time.

 

[tactps]

When you do your calc, use the following if statement, assuming that B1 contains the written-down-value:

=if(B1<0,"",your_formula)

You will then not see the cells where the WDV is less than 0.

Does that help?

 

[bw60120]

B1 contains my Principle amount. What do you mean by "your formula"?

 

[bw60120]

Also, in what cell do I enter this IF formula?

 

[RalphA]

Here is one way. My first column has the ordinal number of the periods. Say that, in cell A4 I have the % rate of interest, and in cell B4 I have the number of periods. Then:
In cell A6, I have the label, "PERIOD"
In cell A7 I enter 1, for period 1
In cell A8, I enter,

=IF(A7<B$4,A7+1,"")

and then copy down to cell 367.
Only the periods 1 through B4 will show.

I use the same

IF(A7<B$4,blabla,"")

to make the subequent cells in columns B, C etc also not visible.

 

[bw60120]

That works, but I also have a column for Number Of Years. So I need to somehow multiplt the number of periods by the number of years. How can I do that?

 

[bw60120]

Nevermind guys. I think I've figured it out. Thanks for all your help!! I may be back later on this semester :wink: :wink: . Thanks again.