Q&A for How to Prepare Amortization Schedule in Excel

Return to Full Article

Coming soon

Add New Question

Question

How can I change the currency?

Community Answer

The currency is dependent on the format of the cell with dollar amounts in them. Right click on the cell you want to change and click on "Format." Then choose "Currency" and put in the appropriate codes for the currency you choose.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 3 Helpful 14
Question

How do I do a weekly payment loan?

Community Answer

You have to change the periods. Try this: Change "Months" in cell A3 to "Periods." Change the "Payments" formula in cell B4, changing the "12" (which represents months) to "52" which represents the number of weeks in a year. The new formula will look like this: "=ROUND(PMT($B$2/52,$B$3,-$B$8,0), 2)." You'll also need to change your periods starting in cell A9 to add 7 days instead of 1 month. I would use this formula in cell A9: "=DATE(YEAR(A8),MONTH(A8),DAY(A8)+7." Lastly, copy your new formula down to the rest of the schedule so all the periods will be correct.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 11 Helpful 27
Question

How do I change the formula for quarterly payments rather than monthly payments?

Community Answer

Wherever there is a field being populated with the =ROUND(...) formula, replace the "12" entry with a "4." Example: In cell E8, create a formula to calculate the loan interest amount on the beginning balance for that period. The formula will look like "=ROUND($B8*($B$2/4), 2)"

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 3 Helpful 12
Question

Shouldn't F9 & G9 be a total of the previous month's and this month's principle and interest?

Community Answer

Yes it should.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 12 Helpful 26
Question

What if payment amounts vary, and dates of payments vary, can interest be computed daily to the date of payment and specific amount of actual payment?

Community Answer

Yes, but it is a lot more work. The schedule in this article was designed for a fixed payment amount with fixed interest, and fixed payment periods. You can change the formula of any cell to account for new information. Go to the first change in payment, interest, or period. On that line, change the formula accordingly and then copy to the rest of the schedule.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 15 Helpful 27
Question

How can I build an amortization table without interest?

Community Answer

Use the schedule above, but put the interest to zero (0) %.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 7 Helpful 15
Question

The final balance is near zero, but not exactly zero. All cells are correct. Is this a rounding issue?

Community Answer

As long as it is within a penny for each month of the period, it is okay, i.e. 30 years = $3.60.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 12 Helpful 22
Question

Can I calculate the number of months with a predetermined repayment?

Community Answer

Yes. You can calculate how many months remain on a loan by doing a "NPER" function. Use the following formula: (Loan Balance, Interest Rate, and Payment)=NPER (Rate/12, Payment, Loan Balance).

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 31 Helpful 29
Question

How do I add a column for additional principle payments?

Community Answer

Add a new column I and label it "Additional Principal." Any month that you make an additional principal payment, put the amount of the additional principal in this new column I. Next, change the formula in cell E8 from "=B8-D8" to "B8-D8-I8." Finally, copy the formula in E8 into every cell below it just like you did when you copied the first line into every cell until about 370 or so. With nothing in the I column, the formula acts just like the original formula.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 176 Helpful 96
Question

How do I add a calculation for amortized property taxes?

Community Answer

This table allows you to vary the interest rate, the principal, and the term of the loan. Generally, the property taxes and insurance costs are escrowed (meaning monthly portions are included with each loan payment), so they don't vary. However, if you wanted to see the total monthly payment including escrowed amounts, you could include a couple of rows at the top of the table. In cell A5, add the label "Monthly Escrow Payment" and in cell A6 add the label "Total Monthly Payment". Cell B5 would contain your property tax amount. Cell B6 would be the formula =B4+B5.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 10 Helpful 6
Question

How do I create an amortization schedule if I know the monthly payment, but not how many months?

Community Answer

The NPER function will help you find how many months. NPER (rate/period, payment, balance). The payment for the loan is input as a negative value.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 0
Question

I am using the Microsoft amortization schedule provided in the examples and I am trying to take the formulas in the columns and populate rows so that I can create a real estate investment spread sheet. Can I do it without going cell by cell?

Community Answer

You only have to enter the formulas in the first row or two. Step 10 describes how to copy and paste it to the rest of your rows.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 0
Question

Why don't I fill in F8 and G8? That doesn't seem right to me.

Community Answer

I agree. I put =$D8 in F8 and =$E8 in G8.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 0
Question

How do I account for $50 late fee for each month a payment is late?

Community Answer

You can use the "additional payments" answer above. It's the same thing, but a negative instead of a positive.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 1
Question

How do I add a $ sign to the interest and cumulative interest columns?

Community Answer

Format the cells that contain interest as Currency. Highlight the cells you want to have it and select the $ symbol from the Number menu.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 0
Question

What can I do when I think I have a wrong ending balance?

Community Answer

Grab a calculator. Go to the cell containing the suspicious balance, verify that the formula is correct, and manually compute the formula on the calculator. If the calculator gives the same number as what is in the cell, then the problem is a bad formula. If the calculator gives a different number than the balance you suspect, go to the cells that are in the formula and repeat this procedure on each one.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 3 Helpful 0
Question

How can I amortize a loan for 7 years using a specific payment amount?

Community Answer

You can use the amortization scheduled as described with a few changes. First, change the number of months from '360' to 7*12 or '84'. Then change the interest to zero (0). This should give you the answer you are looking for.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 3 Helpful 0