New in Wiki  Comparing Investments
New in Wiki  Comparing Investments
I may have overachieved. Long story short, I ended up with a wiki page to explain how to use a spreadsheet for comparing 2 investments. I thought it would be useful to understand how to do comparisons on your own  DIY investing.
 Are the graphs drawn correctly? I want to make this as simple as possible (but not too simple...). If I'm wrong, or if there's something better, let me know. This is a learning experience. It's no big deal to change them.
 Are the examples correct? Perhaps the examples could be rephrased to be more appropriate for the forum. Example 4 is from a forum thread.
 Is this page too complicated for a new user? I didn't want to dump a textbook here. Just show a few examples to answer common questions on how to do this yourself.
Please see Comparing Investments on the Bogleheads Wiki.
Right now, nothing is linking to this wiki page. I wanted to make sure that it's correct. Also, that it's useful to someone. If this page is not useful or too complicated, that's OK as well.
Comments / questions are welcome.
(Fundamental Property #1 is the basis for the price/yield curve, which is what I was after in the first place.)
 Are the graphs drawn correctly? I want to make this as simple as possible (but not too simple...). If I'm wrong, or if there's something better, let me know. This is a learning experience. It's no big deal to change them.
 Are the examples correct? Perhaps the examples could be rephrased to be more appropriate for the forum. Example 4 is from a forum thread.
 Is this page too complicated for a new user? I didn't want to dump a textbook here. Just show a few examples to answer common questions on how to do this yourself.
Please see Comparing Investments on the Bogleheads Wiki.
Right now, nothing is linking to this wiki page. I wanted to make sure that it's correct. Also, that it's useful to someone. If this page is not useful or too complicated, that's OK as well.
Comments / questions are welcome.
(Fundamental Property #1 is the basis for the price/yield curve, which is what I was after in the first place.)
I reworded Example 3 and added references.
 Taylor Larimore
 Advisory Board
 Posts: 30408
 Joined: Tue Feb 27, 2007 8:09 pm
 Location: Miami FL
Comparing past performance
Hi Lady:
You start off by saying,
I also think that past performance figures should have the warning: "Past performance is no guarantee of future performance."
You start off by saying,
I would change to read: "This article shows how to compare the past performance of two investments.""This article shows how to compare two investments."
I also think that past performance figures should have the warning: "Past performance is no guarantee of future performance."
"Simplicity is the master key to financial success."  Jack Bogle
Insightful thought
As usual, Taylor's editorial suggestion goes to the essential concept.
Thanks!
Shawcroft
Thanks!
Shawcroft
Is there any way you could redraw the graphs to visually show that compound interest produces exponential, not linear, growth?
Ron
Ron
Money is fungible 
Abbreviations and Acronyms
Rager1  I had no idea if this page was helpful or not. You answered my question  it is. Thanks!
The reason you couldn't find it is because I intentionally did not link it to the rest of the wiki pages (note that it's in the development category, at the bottom of the page). It's still a work in process.
I'm asking for forum assistance to help proofread this page because I'm not an expert on the topic (my background is engineering, not finance).
I restored the "Under Construction" flag at the top of the page. Although the answers match the textbook, I need to add some fundamental material suggest by the forum members.
To do:
Explanation of the formula sign conventions.
Add cash flow diagrams.
Oicuryy  I updated the graphs to show that compound interest rate grows exponentially. I also removed the labels that showed the interest rate "angle"  it probably hurt more than helped. (Refresh your browser if you don't see any changes.)
Thanks to the limitations of PowerPoint, the best I could do was an arc, which is a sinusoidal curve. I hope that's close enough. Otherwise, I'll have to graph it in Excel.
Comments / questions / corrections are welcome.
The reason you couldn't find it is because I intentionally did not link it to the rest of the wiki pages (note that it's in the development category, at the bottom of the page). It's still a work in process.
I'm asking for forum assistance to help proofread this page because I'm not an expert on the topic (my background is engineering, not finance).
I restored the "Under Construction" flag at the top of the page. Although the answers match the textbook, I need to add some fundamental material suggest by the forum members.
To do:
Explanation of the formula sign conventions.
Add cash flow diagrams.
Oicuryy  I updated the graphs to show that compound interest rate grows exponentially. I also removed the labels that showed the interest rate "angle"  it probably hurt more than helped. (Refresh your browser if you don't see any changes.)
Thanks to the limitations of PowerPoint, the best I could do was an arc, which is a sinusoidal curve. I hope that's close enough. Otherwise, I'll have to graph it in Excel.
Comments / questions / corrections are welcome.
Thanks for changing the graphs. I had already seen the update and did not notice that the curves were not exponential. To me, the original graphs looked too much like graphs for simple interest.LadyGeek wrote:Oicuryy  I updated the graphs to show that compound interest rate grows exponentially. I also removed the labels that showed the interest rate "angle"  it probably hurt more than helped. (Refresh your browser if you don't see any changes.)
Thanks to the limitations of PowerPoint, the best I could do was an arc, which is a sinusoidal curve. I hope that's close enough. Otherwise, I'll have to graph it in Excel.
Ron
Money is fungible 
Abbreviations and Acronyms
I added a section for cash flow sign convention and a placeholder for cash flows.
FYI  The signs are swapped in Example 1. I'll fix that later.
On the assumption that no question is too simple:
 My textbook clearly states that when calculating annualized yields (page 40), the simple act of multiplying the rate by the frequency of payments results in an error.
For example, divide the annual interest rate by 2 to get the semiannual interest rate. Alternately, multiply the semiannual interest rate by 2 to get the annual rate. (I see division by 12 for monthly rates as well.)
The formula is: Effective annual yield = (1 + Periodic Interest Rate)^m 1
where m = Frequency of payments per year.
Why is the simple multiplication done (rate*2 or rate*12) instead of using the right formula? I see this in bonds and just about everywhere. I'm missing something basic and feel I should offer an explanation in the section on Compounded Interest Rates, but I don't know what to do.
Please see Comparing Investments on the Bogleheads Wiki.
Is this web site a good reference? Microsoft Excel as a Financial Calculator Part I.
FYI  The signs are swapped in Example 1. I'll fix that later.
On the assumption that no question is too simple:
 My textbook clearly states that when calculating annualized yields (page 40), the simple act of multiplying the rate by the frequency of payments results in an error.
For example, divide the annual interest rate by 2 to get the semiannual interest rate. Alternately, multiply the semiannual interest rate by 2 to get the annual rate. (I see division by 12 for monthly rates as well.)
The formula is: Effective annual yield = (1 + Periodic Interest Rate)^m 1
where m = Frequency of payments per year.
Why is the simple multiplication done (rate*2 or rate*12) instead of using the right formula? I see this in bonds and just about everywhere. I'm missing something basic and feel I should offer an explanation in the section on Compounded Interest Rates, but I don't know what to do.
Please see Comparing Investments on the Bogleheads Wiki.
Is this web site a good reference? Microsoft Excel as a Financial Calculator Part I.

 Posts: 1158
 Joined: Fri Dec 12, 2008 8:02 pm
for typical interest rates, the multiplicative formula is close enough to the exponential, and it produces the result with a lot less work if you don't have a calculator that can do exponentials and logarithms.LadyGeek wrote:Why is the simple multiplication done (rate*2 or rate*12) instead of using the right formula?
For instance, 3% per annum is 0.25% per month by division, and 0.246627% per month by exponential  a difference of only 0.0034 percentage points.
Going the other way, 0.25% per month is 3% by multiplication or 3.041596% by exponential.
I suspect that the use of simple multiplication in certain contexts (such as monthly vs. annual interest rates) is traditional and dates back to the precalculator era.
 dratkinson
 Posts: 5308
 Joined: Thu Jul 26, 2007 6:23 pm
 Location: Centennial CO
Lady,sommerfeld wrote:for typical interest rates, the multiplicative formula is close enough to the exponential, and it produces the result with a lot less work if you don't have a calculator that can do exponentials and logarithms.LadyGeek wrote:Why is the simple multiplication done (rate*2 or rate*12) instead of using the right formula?
For instance, 3% per annum is 0.25% per month by division, and 0.246627% per month by exponential  a difference of only 0.0034 percentage points.
Going the other way, 0.25% per month is 3% by multiplication or 3.041596% by exponential.
I suspect that the use of simple multiplication in certain contexts (such as monthly vs. annual interest rates) is traditional and dates back to the precalculator era.
Just checked my HP12C and it is also wrong: It claims 12% APY is 1% per month (using the button functions). Would never have known it was wrong without your linked post to the college lecture and the rate computation.
However, it would be nice to have an explanation from a definitive source. Surely there is some justification to which HP, Excel, and others point to when they take this shortcut. Otherwise their answers would not agree.
Or maybe their answers don't agree... and it's just that the disagreement is too small to matter.
Or maybe I'm wrong. http://www.expertlaw.com/forums/showthread.php?t=67596
Requester here say:
Code: Select all
Can someone please explain how the following language written in a security agreement (Mortgage of Chattels) should be calculated?
“[…..]In installments as herein stated, for value received, I promise to pay to [Lender/Payee], or at a place to be designated by the payee, TWENTY FIVE THOUSAND FIVE HUNDRED AND NO/100 DOLLARS, with interest from [first payment date], on unpaid principal at the rate of twelve (12%) percent per annum; principal and interest payable in installments of $300.00 or more on the same day of each and every month, beginning on the [0th] day of [month & year] and continuing until paid in full.”
One of my bankers told me he thinks it should be calculated at 0.949% per month because it compounds annually. The Lender/Payee thinks it should be calculated at 1% per month because it compounds monthly. The gap between the two methods of calculating this interest rate is $3,172.53.
PV = 25,500
FV = 0
PMT = 300
I = 1%
Type, End of period payment (assume first payment due one month after loan date)
N = 191+, so 192 months, 16 years, to repay
<conspiracy theory alert>
So the lender convention incorporated into my HP calculator, 12% APY/12 = 1% per month, benefits the money lenders by overstating the monthly periodic interest rate. In this case, a little more than $3100 on a 16 year loan of $25,500. So the small error is not chump change?
</conspiracy theory alert>
I truly did not know this.
I wonder how a BH should handle the next loan document?
(1) Accept the small error built into the periodic interest rate to make things easy for the lender?
(2) Insist we be allowed to insert an accurate computation of the periodic interest rate?
Lady, I feel another Wiki topic coming on for you: The computation of period interest rates based upon the APY and valid financial principles.
Will also need the Wiki page to be printable so we can carry it to the lender.
After all, if we don't try, the answer is automatically "No".
/r
David
It depends on whether you want the annual rate or the annualized yield.LadyGeek wrote:On the assumption that no question is too simple:
 My textbook clearly states that when calculating annualized yields (page 40), the simple act of multiplying the rate by the frequency of payments results in an error.
For example, divide the annual interest rate by 2 to get the semiannual interest rate. Alternately, multiply the semiannual interest rate by 2 to get the annual rate. (I see division by 12 for monthly rates as well.)
The formula is: Effective annual yield = (1 + Periodic Interest Rate)^m 1
where m = Frequency of payments per year.
Why is the simple multiplication done (rate*2 or rate*12) instead of using the right formula? I see this in bonds and just about everywhere. I'm missing something basic and feel I should offer an explanation in the section on Compounded Interest Rates, but I don't know what to do.
By definition, the periodic rate is the annual rate divided by the number of periods per year. So multiply the periodic rate by the number of periods per year to get the annual rate.
Use your formula to get the annualized yield.
Excel's RATE and IRR functions return the periodic rate. But XIRR returns the annualized yield.
Ron
Last edited by Oicuryy on Mon Mar 15, 2010 12:21 pm, edited 1 time in total.
Money is fungible 
Abbreviations and Acronyms
choosing a discount rate
Very nice page describing present discounted value of cash flows. This concept applies well to bonds and mortgages, which have a known interest rate. I especially liked the $140K in 3 years or $160K in 5 years example.
When applying this concept to other investments such as stocks a critical issue is how to go about choosing the appropriate discount rate for the security in question. Especially when the returns are random. This is a question that I have not been able to find a good answer to. You may want to address this issue in your wiki page.
Here is another related reference I found interesting: http://en.wikipedia.org/wiki/Net_present_value
 Joppy
When applying this concept to other investments such as stocks a critical issue is how to go about choosing the appropriate discount rate for the security in question. Especially when the returns are random. This is a question that I have not been able to find a good answer to. You may want to address this issue in your wiki page.
Here is another related reference I found interesting: http://en.wikipedia.org/wiki/Net_present_value
 Joppy
dratkinson  Thank you for the PMs. I've updated Examples 1 and 2 with your suggestions and everything seems much more clear. I have some material to review and will take a stab at creating a cash flow wiki page and inserting the diagrams into the examples.
(The Lehigh link is: Cash Flow & Interest, a PowerPoint presentation. )
As for your mortgage example, could you please show the extra steps? I used NPER() to get N=190.5 periods and the 0.9489% monthly interest rate, but could not reproduce the $3,172.53 rate difference. This type of example may be too complicated for an introductory page (?).
The effective yield example (Example 5) was incorrect. Fixed.
===================
OT  As for mortgage roundoff errors, all I can think of is Office Space.
Please see Comparing Investments on the Bogleheads Wiki.
(The Lehigh link is: Cash Flow & Interest, a PowerPoint presentation. )
As for your mortgage example, could you please show the extra steps? I used NPER() to get N=190.5 periods and the 0.9489% monthly interest rate, but could not reproduce the $3,172.53 rate difference. This type of example may be too complicated for an introductory page (?).
You were correct. I didn't understand the difference between interest rate and yield. I took your suggestions and rewrote the section on Compounded interest rates.Oicuryy wrote:It depends on whether you want the annual rate or the annualized yield.
By definition, the periodic rate is the annual rate divided by the number of periods per year. So multiply the periodic rate by the number of periods per year to get the annual rate.
Use your formula to get the annualized yield.
Excel's RATE and IRR functions return the periodic rate. But XIRR returns the annualized yield.
Ron
First, thanks for the wiki updates. I put this material in the wiki as part of the compounded interest rewrite.sommerfeld wrote:for typical interest rates, the multiplicative formula is close enough to the exponential, and it produces the result with a lot less work if you don't have a calculator that can do exponentials and logarithms.
For instance, 3% per annum is 0.25% per month by division, and 0.246627% per month by exponential  a difference of only 0.0034 percentage points.
Going the other way, 0.25% per month is 3% by multiplication or 3.041596% by exponential.
I suspect that the use of simple multiplication in certain contexts (such as monthly vs. annual interest rates) is traditional and dates back to the precalculator era.
The effective yield example (Example 5) was incorrect. Fixed.
Nice suggestion, but I don't think it should go in this page. It's intended to introduce basic concepts  how to select a discount rate is probably for another page. It's not a problem, but this is not my area. Perhaps asking in a dedicated forum thread would be a better approach (with a better title) so you can get your question to the right experts. If there's enough of a response, perhaps a wiki page could be written from the answers. There's also a Suggestions for the Wiki thread. I added the NPV reference.joppy wrote:...When applying this concept to other investments such as stocks a critical issue is how to go about choosing the appropriate discount rate for the security in question. Especially when the returns are random. This is a question that I have not been able to find a good answer to. You may want to address this issue in your wiki page... Joppy
===================
OT  As for mortgage roundoff errors, all I can think of is Office Space.
Please see Comparing Investments on the Bogleheads Wiki.
 dratkinson
 Posts: 5308
 Joined: Thu Jul 26, 2007 6:23 pm
 Location: Centennial CO
I am not a financial expert, nor do I play one on TV. If my calculator manual doesn't have an example, I'm lost.LadyGeek wrote:dratkinson
As for your mortgage example, could you please show the extra steps? I used NPER() to get N=190.5 periods and the 0.9489% monthly interest rate, but could not reproduce the $3,172.53 rate difference. This type of example may be too complicated for an introductory page (?).
I did as you, extracted the numbers from the linked story (PV, I, PMT, and FV), punched them in and computed 191 full payments with a remaining amount of about $100 left over for a 192d payment. It looked like a 16 year loan so that what I called it.
Computed total loan repayment (N * PMT, plus the remaining partial payment) with periodic interest rate set to 1% and .948%. The story's loan agreement says PMT is to be $300+, but I computed a PIR of .948% should have resulted in a PMT of slightly less than $300... so that is what I used. A smaller PMT (less than $300) should have resulted in the greatest difference between the two methods.
I too could not reproduce the story's claimed $3,172.53 loan repayment difference. But I did compute a difference of over $1000, which to me is also a significant amount.
Only included the linked story and account of the repayment difference to show that the claimed very small difference in the methods used to compute the PIR can make a significant difference in total loan repayment... and it appears to be in the interest of the lenders.
Wonder if lenders would be so willing to accept an approximation of anything if it when against their favor?
Bottom line. From now on, if a lender tells me not to worry about the very small difference in the way the PIR is computed, I must politely disagree. Guess the strength of my disagreement will be inversely proportional to my need for the loan and the strength of my "new car" fever.
Ma'am, you know, I was blissfully unaware of this builtin lender advantage until you pointed it out.
Maybe some of the financial experts on here could explain the $3K difference to us.
Until then, don't let this little side topic distract you from your primary purpose.
/r
David
It depends on whether 12% is the annual rate or the annualized yield. (I'm starting to sound like a broken record.)dratkinson wrote:Code: Select all
Can someone please explain how the following language written in a security agreement (Mortgage of Chattels) should be calculated? “[…..]In installments as herein stated, for value received, I promise to pay to [Lender/Payee], or at a place to be designated by the payee, TWENTY FIVE THOUSAND FIVE HUNDRED AND NO/100 DOLLARS, with interest from [first payment date], on unpaid principal at the rate of twelve (12%) percent per annum; principal and interest payable in installments of $300.00 or more on the same day of each and every month, beginning on the [0th] day of [month & year] and continuing until paid in full.” One of my bankers told me he thinks it should be calculated at 0.949% per month because it compounds annually. The Lender/Payee thinks it should be calculated at 1% per month because it compounds monthly. The gap between the two methods of calculating this interest rate is $3,172.53.
The lender says 12% is the annual rate. So the monthly rate is
=0.12/12
And the number of payments is
=NPER(0.12/12,300,25500)
The banker says 12% is the annualized yield. So, from Example 5, the monthly rate is
=NOMINAL(0.12,12)/12
And the number of payments is
=NPER(NOMINAL(0.12,12)/12,300,25500)
Multiply the difference in number of payments by $300 to get the difference in total amount paid. I did not get $3,172.53 either, but the borrower might have made some payments that were more than $300.
It's anybody's guess which way a judge will decide.
Ron
Money is fungible 
Abbreviations and Acronyms
I thought I was doing something wrong. After all this work, I get stuck on a simple problem. Turns out it wasn't so simple after all. My confidence is restored.
dratkinson  I used your mortgage example in the wiki page, but in a context that says "beware of approximations" and point back to this thread for more info.
I think this page is ready for prime time. I did some cleanup (formatting) and added a "Needs a diagram" flag with a note that the cash flow diagrams are in process. (I won't be able to get to the cash flow diagrams until the end of the week.)
It's not finished, but I think it has enough information to be useful asis. This page is now part of the Investing StartUp kit, note the sidebar at the right side of the page.
Please see Comparing Investments on the Bogleheads Wiki.
Please see Bogleheads Investing StartUp Kit on the Bogleheads Wiki.
Comments / suggestions / corrections are welcome. Wiki editors should edit the page directly.
dratkinson  I used your mortgage example in the wiki page, but in a context that says "beware of approximations" and point back to this thread for more info.
I think this page is ready for prime time. I did some cleanup (formatting) and added a "Needs a diagram" flag with a note that the cash flow diagrams are in process. (I won't be able to get to the cash flow diagrams until the end of the week.)
It's not finished, but I think it has enough information to be useful asis. This page is now part of the Investing StartUp kit, note the sidebar at the right side of the page.
Please see Comparing Investments on the Bogleheads Wiki.
Please see Bogleheads Investing StartUp Kit on the Bogleheads Wiki.
Comments / suggestions / corrections are welcome. Wiki editors should edit the page directly.
 dratkinson
 Posts: 5308
 Joined: Thu Jul 26, 2007 6:23 pm
 Location: Centennial CO
Eureka! Ron has solved the mystery!Oicuryy wrote: It depends on whether 12% is the annual rate or the annualized yield. (I'm starting to sound like a broken record.)
The lender says 12% is the annual rate. So the monthly rate is
=0.12/12
And the number of payments is
=NPER(0.12/12,300,25500)
The banker says 12% is the annualized yield. So, from Example 5, the monthly rate is
=NOMINAL(0.12,12)/12
And the number of payments is
=NPER(NOMINAL(0.12,12)/12,300,25500)
Multiply the difference in number of payments by $300 to get the difference in total amount paid. I did not get $3,172.53 either, but the borrower might have made some payments that were more than $300.
It's anybody's guess which way a judge will decide.
Ron
The borrower was paying more than the minimum $300 per month. This explains the discrepancy between his $3K difference and mine (admittedly wrong because I assumed a PMT of slightly less than $300... which can not be... but would exaggerate the discrepancy.)
Worst (best) case example. Borrow $25K, as above, and expect to repay a total of ~$50K over 16 years.
But instead, repay the loan the next day for $25K (plus some few dollars for 1day's interest). The $25K difference in avoided interest is explained by the increased PMT amountresulting in fewer N payments, not the I rate.
Are we good, or what?
The exact amount of the borrower's PMT, which resulted in his computed $3K repayment difference between the two periodic interest rates, is left as a student exercise.
/r
David
Maybe this problem could be interpreted a bit differently. There's no fixed maturity date  the note says that payments continue until the principal is paid. The number of periods (NPER) is a variable. The hint that the total payment should be around 56,000 gave me more confidence to try again.
FV: 25,500.00
I: 1%
PMT: 300
NPER (Cell B20): 190.66 =NPER(1%,300,25500), but use 191.00 =ROUNDUP(B18,0) for Excel CUMIPMT, CUMPRINC
Total Payments (principal + interest): 57,197.70 =ABS(300 * B18), ignore sign
Total interest: 31765.77 = CUMIPMT(1%,191,25500,1,191,0)
Total principal: 25500 =CUMPRINC(1%,191,25500,1,191,0), Agrees with FV
Total Excel principal + interest: 57265.77 = 31765.77  25500
Truncation error from Excel: 68.07 =57265.77  57197.7
Borrower's Interpretation (annual yield, compounded monthly)
FV: 25,500.00
I (Cell B26): 0.949% = NOMINAL(B3,12)/12
PMT: 300
NPER (Cell B28): 173.94 = NPER(B26,300,25500), but use 174.00 =ROUNDUP(B28,0) for Excel CUMIPMT, CUMPRINC
Total Payments (principal + interest): 52,182.75 =ABS(300 * B28), ignore sign
Total interest: 26693.20 = CUMIPMT(B26,174,25500,1,174,0)
Total principal: 25500 =CUMPRINC(1%,174,25500,1,174,0), Agrees with FV
Total Excel principal + interest: 52193.20 = 26693.2  25500
Truncation error from Excel: 10.45 = 52193.2  52182.75
Total interest overpayment: 5072.57 = 31765.77  26693.2
Note: CUMIPMT, CUMPRINC: Excel truncates Nper, start_period, end_period, and type to integers.
=====================================
There are 2 differences here. Not only the interest overpayment, but an extra 11/2 years on the length (maturity date) of the loan. Caveat emptor.
I updated the wording in the wiki and included this possible solution with the example problems (download link at the bottom of the page).
Update: Comments revised to indicate that this solution is now supplied with the wiki examples. I also rephrased my remarks about this exercise.
Lender's Interpretation (annual rate)...in installments of $300.00 or more on the same day of each and every month...and continuing until paid in full.
FV: 25,500.00
I: 1%
PMT: 300
NPER (Cell B20): 190.66 =NPER(1%,300,25500), but use 191.00 =ROUNDUP(B18,0) for Excel CUMIPMT, CUMPRINC
Total Payments (principal + interest): 57,197.70 =ABS(300 * B18), ignore sign
Total interest: 31765.77 = CUMIPMT(1%,191,25500,1,191,0)
Total principal: 25500 =CUMPRINC(1%,191,25500,1,191,0), Agrees with FV
Total Excel principal + interest: 57265.77 = 31765.77  25500
Truncation error from Excel: 68.07 =57265.77  57197.7
Borrower's Interpretation (annual yield, compounded monthly)
FV: 25,500.00
I (Cell B26): 0.949% = NOMINAL(B3,12)/12
PMT: 300
NPER (Cell B28): 173.94 = NPER(B26,300,25500), but use 174.00 =ROUNDUP(B28,0) for Excel CUMIPMT, CUMPRINC
Total Payments (principal + interest): 52,182.75 =ABS(300 * B28), ignore sign
Total interest: 26693.20 = CUMIPMT(B26,174,25500,1,174,0)
Total principal: 25500 =CUMPRINC(1%,174,25500,1,174,0), Agrees with FV
Total Excel principal + interest: 52193.20 = 26693.2  25500
Truncation error from Excel: 10.45 = 52193.2  52182.75
Total interest overpayment: 5072.57 = 31765.77  26693.2
Note: CUMIPMT, CUMPRINC: Excel truncates Nper, start_period, end_period, and type to integers.
=====================================
There are 2 differences here. Not only the interest overpayment, but an extra 11/2 years on the length (maturity date) of the loan. Caveat emptor.
I updated the wording in the wiki and included this possible solution with the example problems (download link at the bottom of the page).
Update: Comments revised to indicate that this solution is now supplied with the wiki examples. I also rephrased my remarks about this exercise.
Cash flow diagrams are now incorporated. I added an overview and diagrams for Examples 1 and 2. It's more work to add them for the remaining examples, but I was wondering if that would be too much for the wiki page. They're more complicated and I only wanted to show the basics.
(Thanks to dratkinson for all the assistance via PM.)
The HP12C calculator manual has a very good tutorial on cash flow diagrams (link at bottom of page, from HP).
I added Excel's XIRR function, as there was a recent forum thread that gave a lot of insight for calculation of investment returns.
Please see Comparing Investments on the Bogleheads Wiki.
(Thanks to dratkinson for all the assistance via PM.)
The HP12C calculator manual has a very good tutorial on cash flow diagrams (link at bottom of page, from HP).
I added Excel's XIRR function, as there was a recent forum thread that gave a lot of insight for calculation of investment returns.
Please see Comparing Investments on the Bogleheads Wiki.
 Taylor Larimore
 Advisory Board
 Posts: 30408
 Joined: Tue Feb 27, 2007 8:09 pm
 Location: Miami FL
I second Taylor's WOW!
LadyGeek, the final product is really informative and I've already found uses for it. Thanks again for all of your work, and to the other Bogleheads who made helpful suggestions.
This is another example of how I learn something from the Boglehead's website each and every week.
Ed
LadyGeek, the final product is really informative and I've already found uses for it. Thanks again for all of your work, and to the other Bogleheads who made helpful suggestions.
This is another example of how I learn something from the Boglehead's website each and every week.
Ed
Thanks! The intent is to help investors who don't have a business background (like me) understand the basic principles of investments.
While writing this page, I discovered that I didn't understand:
 what a cash flow was
 that there are 6 basic financial variables
 the cash flow sign convention
Now, I'm getting a lot more insight into the forum discussions. Especially bonds (I'm working on it, this info is a big help.)
While writing this page, I discovered that I didn't understand:
 what a cash flow was
 that there are 6 basic financial variables
 the cash flow sign convention
Now, I'm getting a lot more insight into the forum discussions. Especially bonds (I'm working on it, this info is a big help.)