[jessica7smile]

I am building the amortization schedule in excel, and I am confused with some stuff.

Company A purchased from Company B on January 1, 2015 equipment to expand its production capacity.Assume that the stated interest rate is 7% when the prevailing interest rate for such obligations was 10%. The face value of the note is now $950,000, due in 6 years. will pay the entire $ 950,000 at the end of 6 years. The present value of the note is $708,905. Determine the market rate of the note. (the Internal Rate of Return or yield).

Interest Periods Note Payable Cash Interest Interest Expense Discount on Balance of

Date Cash Payment N/P Unamortized Discount

January 1, 2015 $241,095

December 31, 2015 $66,500 $95,160 $28,660 $212,435

December 31, 2016 $66,500 $99,007 $32,507 $179,927

December 31, 2017 $66,500 $103,371 $36,871 $143,056

December 31, 2018 $66,500 $108,320 $41,820 $101,236

December 31, 2019 $66,500 $113,934 $47,434 $53,802

December 31, 2020 $950,000 $66,500 $120,302 $53,802 $-

$950,000 $640,095 $241,095.00

I had no problem until this part, but I was a little bit confused about the Present Value of the Note parts.

I assumes the Present Value of the Note was negative because primary focus of this question was calculating IRR, so I thought I should put initial cash outflows as negative.

Interest Periods Present Value Cash Flows

Dates of the Note

January 1, 2015 $(708,905) $(708,905)

December 31,2015 $737,565 $66,500

December 31,2016 $770,073 $66,500

December 31,2017 $806,944 $66,500

December 31,2018 $848,764 $66,500

December 31,2019 $896,198 $66,500

December 31,2020 $950,000 $1,016,500

Can anyone tell me if my understanding is correct and help me with amortization schedule concept?

[Anonymous]

Would you be willing or able to upload what you're doing into something like Google Spreadsheets and share it from there so that we can see how you're doing it? It's hard to look at spreadsheets in something that's pasted as text only. At bare minimum, if you can upload a screenshot of the spreadsheet to imgur, that will help, but we still won't have your formulas and such to be able to give input on them.

It's been awhile since I've done an amortization schedule in Excel, but it seems like I did have to make something negative in a weird place to make it work, like doing “opposite of pmt” for the formula instead of just using “pmt”, since it calculated it as a negative number. (I did one for my house and one for my car, just to know the exact impact of each and every additional principal payment. 😛 ) I could create an amortization table to see again exactly how it's done, but since you have one created, it'd be a lot simpler for us to look at yours in Google Spreadsheets and give suggestions to tweak it, rather than “reinventing the wheel” to get one to play with.

[jessica7smile]

Can you tell me how to upload the excel file here? this website is so confusing to me. It seems like it doesn't even let me edit or delete the post.

[Anonymous]

You can try Imgur just to showcase your spreadsheet:

https://imgur.com/

[Author]

Replies