Amortization schedule for multiple loans

[nwfunk]

Hello,

I need to create an amortization table for several thousand loans. I could create a seperate amortization schedule for each loan, but as you can imagine, doing so would take a long time.

These loans have varying interest rates, loan periods, and beginning balances, so it is not as if I could just add the beginning balances together and amortize on that basis.

It seems like this will require a VBA macro, however my programming skills are a little rusty.

Any advice would be greatly appreciated.

 

[sheetspread]

The most compact format would have each loan on a row and each payment date in a column, or two columns for each period (Interest/Principal). If you want to put the payment dates in rows below each loan (for a very long single spreadsheet) or divide them into separate worksheets that can be done also. Can you provide a sample of your input data and approximate preferred appearance?

 

[nwfunk]

Generally speaking, I would receive a single excel file that contains two tabs:

Tab #1 - loan-specific information for a "n" number of loans - loan#, interest rate, present value, # of payment periods remaining, payment. This information is organized into different columns, loan#(A), interest rate (B), present value(C), # of payment period remaining(D), payment(E). Each row represents an individual loan's details.

Tab #2 - I must build an aggregated amortization table in this tab. The format of this table will follow a common amortization table - period(A2), beginning balance (B2), payment(C2), interest(D2), principal(E2), ending balance(F2).

I can send you an excel doc if it would make things easier.

Thanks.

 

[sheetspread]

Ok, I sent my email address to your private message box.

 

[Zachy]

Hi! I am in the same situation! Could you/anyone share an excel template for the amortisation of multiple loans? I am already familiar with the amortisation schedule for one loan

But I can't figure how to create a compact, multi-loan amortisation schedule. Any help is greatly appreciated!

 

[Peter_SSs]

Hi! I am in the same situation! Could you/anyone share an excel template for the amortisation of multiple loans? I am already familiar with the amortisation schedule for one loan

But I can't figure how to create a compact, multi-loan amortisation schedule. Any help is greatly appreciated!

Welcome to the MrExcel board!

Duplicate to: Multi-Loan Amortisation Schedule

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this older thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.