Amortization Schedule

bw60120

New Member
Joined
Feb 2, 2005
Messages
5
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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?
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829

ADVERTISEMENT

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,
Code:
=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
Code:
IF(A7<B$4,blabla,"")
to make the subequent cells in columns B, C etc also not visible.
 

bw60120

New Member
Joined
Feb 2, 2005
Messages
5
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

New Member
Joined
Feb 2, 2005
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top