ROUND function

[leopardhawk]

Hello forum friends, the formula below returns the result I need but, it isn't rounded to two decimal places. Instead, it gives me 1342.751667. I have been beating myself up over this trying to figure out what I am doing wrong. Any guidance is appreciated.

=ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),2)+(ROUND((INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084))+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),2)+ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),2)+ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),2))/12

 

[leopardhawk]

@Joe4 thanks, I will try that.

 

[Joe4]

@Joe4 thanks, I will try that.

You are welcome.

I find that if you have something big and complex that doesn't work, one good way to fix it is to start over from scratch, and rewrite it starting just with the first part. When you get that working, add the second, and if that works, add the third, etc.

 

[leopardhawk]

@Joe4 @Misca @AhoyNC Joe, I did as you suggested and re-wrote the formula from scratch, making sure each section worked as I went along. I also got rid of ALL of the ROUND(s) in my original formula. Now everything is working as anticipated. Of course, I'll never really know exactly what was causing the problems but as you said, most likely a misplaced parenthesis or something along those lines. Thanks to all of you that stepped in to try and help me, I am super relieved that this one is now working! Cheers!

Posting the formula below since it's working now (I had to split the formula into two because of the 8192 character limit!!! I know, it's likely some folks on here will laugh and I'm sure there's probably a simpler way to do it but my Excel skills are very limited.

PART 1
=CONCATENATE("Let's assume "&IF(personal_info!C9=0,"Name",personal_info!C9)&" starts "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" CPP at age 65 but continues to work and make CPP contributions. Using the "&YEAR(TODAY())&" maximum retirement benefit of "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&" per month and"," YMPE of "&TEXT(INDEX(CPP_OAS[Thresholds],MATCH("YMPE",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&", we can calculate "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" annual PRB at age 66 at "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&"."," If "&IF(personal_info!C9=0,"Name",personal_info!C9)&" continues to work past age 66, "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" PRB from the previous year is added to the current year's PRB for "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" lifetime and like the CPP, the PRB is indexed to inflation! However, the good news doesn't end there. From age 65 to 70, an additional 8.4% per year (.7% per month) is added to your PRB! So, assuming "&IF(personal_info!C9=0,"Name",personal_info!C9)&" keeps working until "&IF(personal_info!F9="M","he's",IF(personal_info!F9="F","she's","they're"))&" 70...

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")&".

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 68 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*0.025*12*1.084,"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")&".

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 69 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")++TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")&".

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 70 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00")&".")

PART 2
=CONCATENATE("If "&IF(personal_info!C9=0,"Name",personal_info!C9)&" continues to work after reaching age 70, "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" will no longer contribute to CPP but now "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" will receive an additional $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00")&" (indexed to inflation) every year for the rest of "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" life! "&IF(personal_info!C9=0,"Name",personal_info!C9)&"'s monthly CPP retirement benefit is now at $"&ROUND(TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")+(TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00"))/12,2)&".", " If "&IF(personal_info!C9=0,"Name",personal_info!C9)&" lives to 87 years of age, "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" total accumulated PRB (taking inflation into account) would be "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5*3+262.23,"$#,##0.00")&". This is certainly much more than the "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5,"$#,##0.00")&" "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" would have received by not contributing.", " Even if "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" invested the "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5,"$#,##0.00")&" "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" (which we all know most people won't do), "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" would need an unlikely 6% return (compounded annually) to grow it to "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5*3+262.23,"$#,##0.00")&" in the same amount of time."," Careful consideration should be given to how much you would gain by continuing to contribute. And remember, your employer is also matching your contributions on your behalf.")

ReplyForward