Modified amortization worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1506975%22%20slang%3D%22en-US%22%3EModified%20amortization%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1506975%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20some%20help.%20I'm%20trying%20to%20come%20up%20with%20a%20formula%20that%20applies%20a%20payment%20first%20to%20one%20category%20until%20it%20has%20been%20paid%20down%20to%20zero%2C%20and%20then%20moves%20on%20to%20the%20next%20category.%20I%20also%20need%20for%20it%20to%20do%20two%20other%20things%3A%201-%20carry%20over%20any%20remaining%20monies%20to%20the%20next%20category%20and%202-%20calculate%20monthly%20interest.%3C%2FP%3E%3CP%3EMy%20order%20of%20operations%20are%3C%2FP%3E%3CP%3Epayment%20must%20be%20applied%20to%26nbsp%3B%3C%2FP%3E%3CP%3Ecosts%20(%20until%20paid%20down%20to%20zero)%20*%20any%20remaining%20%24%24%20after%20cost%20balance%20is%20zeroed%20out%20goes%20to%26nbsp%3B%3C%2FP%3E%3CP%3Efees%20(until%20paid%20down%20to%20zero)*any%20remaining%20%24%24%20after%20fees%20balance%20is%20zeroed%20out%20goes%20to%3C%2FP%3E%3CP%3Einterest%20(so%20long%20as%20there%20is%20a%20balance%20in%20principal%2C%20this%20category%20changes%20daily%20until%20the%20next%20payment%20is%20made.%20then%20payment%20is%20subtracted%20from%20balance)%20if%20interest%20does%20go%20down%20to%20zero%2C%20the%20remaining%20%24%24%20is%20applied%20to%20principal%2C%20But%20so%20long%20as%20there%20is%20a%20balance%20in%20principal%2C%20interest%20will%20still%20accrue%20until%20principal%20is%20completely%20paid%20off.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20I'm%20not%20confusing%20anyone.%20Somebody%20here%20has%20to%20speak%2Fread%2Fwrite%20%22crazyladyworkneeds%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1506975%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507202%22%20slang%3D%22en-US%22%3ERe%3A%20Modified%20amortization%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F721493%22%20target%3D%22_blank%22%3E%40DaisyB2015%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20safe%20to%20assume%20you%20have%20a%20start%20at%20a%20worksheet%20in%20which%20you're%20trying%20to%20do%20this%3F%20I%20ask%20because%20it%20would%20be%20a%20lot%20easier%20to%20help%20you%20if%20we%20could%20see%20what%20you're%20actually%20working%20with%20(not%20just%20a%20verbal%20image%3B%20not%20even%20a%20visual%20image%3B%20the%20actual%20Excel%20spreadsheet)...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20say%20it%20probably%20won't%20be%20%22a%20formula%22%20--%20as%20in%20a%20%3CU%3E%3CSTRONG%3Esingle%3C%2FSTRONG%3E%3C%2FU%3E%20formula--to%20accomplish%20all%20that.%20It%20might%20be%20possible%2C%20but%20it's%20often%20difficult%20to%20decipher%20complicated%20formulas%2C%20especially%20for%20those%20who%20are%20relatively%20new%20to%20Excel%20and%20it's%20wonders.%20So%20if%20you've%20set%20your%20mind%20on%20somehow%20coming%20up%20with%20a%20single%20formula%2C%20I%20highly%20recommend%20first%20taking%20each%20operation%20and%20doing%20it%20more%20simply%2C%20even%20if%20it%20means%20adding%20columns%20(or%20cells)%20that%20are%20simply%20temporary%20staging%20points%20for%20the%20next%20operation.%20Once%20you've%20done%20all%20that%20to%20the%20point%20where%20it%20all%20works%2C%20you%20can%20start%20working%20to%20combine%20steps%20via%20nested%20formulas%20and%20the%20like.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1509193%22%20slang%3D%22en-US%22%3ERe%3A%20Modified%20amortization%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1509193%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20trying%20to%20help%20me.%20I've%20gone%20ahead%20and%20attached%20what%20I%20will%20hope%20to%20me%20my%20modified%20amortization%20worksheet.%20Again%2C%20I%20appreciate%20any%20and%20all%20help.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1509451%22%20slang%3D%22en-US%22%3ERe%3A%20Modified%20amortization%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1509451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F721493%22%20target%3D%22_blank%22%3E%40DaisyB2015%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGive%20this%20a%20try.%20It%20seems%20to%20me%20it's%20working%20accurately.%20I%20did%20NOT%2C%20however%2C%20check%20all%20your%20formulas.%20The%20only%20ones%20I%20modified%20are%20those%20in%20columns%20K%2C%20L%2C%20M%2C%20N.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20either%20way...if%20it%20doesn't%20work%2C%20let%20me%20know%20what's%20happening%20that%20should%20not%20be.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1509614%22%20slang%3D%22en-US%22%3ERe%3A%20Modified%20amortization%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1509614%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much.%20This%20works%20out%20perfectly%20for%20what%20my%20work%20needs%20are.%20I%20appreciate%20the%20assists.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I need some help. I'm trying to come up with a formula that applies a payment first to one category until it has been paid down to zero, and then moves on to the next category. I also need for it to do two other things: 1- carry over any remaining monies to the next category and 2- calculate monthly interest.

My order of operations are

payment must be applied to 

costs ( until paid down to zero) * any remaining $$ after cost balance is zeroed out goes to 

fees (until paid down to zero)*any remaining $$ after fees balance is zeroed out goes to

interest (so long as there is a balance in principal, this category changes daily until the next payment is made. then payment is subtracted from balance) if interest does go down to zero, the remaining $$ is applied to principal, But so long as there is a balance in principal, interest will still accrue until principal is completely paid off.

 

I hope I'm not confusing anyone. Somebody here has to speak/read/write "crazyladyworkneeds"

4 Replies

@DaisyB2015 

 

Is it safe to assume you have a start at a worksheet in which you're trying to do this? I ask because it would be a lot easier to help you if we could see what you're actually working with (not just a verbal image; not even a visual image; the actual Excel spreadsheet)...

 

I will say it probably won't be "a formula" -- as in a single formula--to accomplish all that. It might be possible, but it's often difficult to decipher complicated formulas, especially for those who are relatively new to Excel and it's wonders. So if you've set your mind on somehow coming up with a single formula, I highly recommend first taking each operation and doing it more simply, even if it means adding columns (or cells) that are simply temporary staging points for the next operation. Once you've done all that to the point where it all works, you can start working to combine steps via nested formulas and the like.

Thank you for trying to help me. I've gone ahead and attached what I will hope to me my modified amortization worksheet. Again, I appreciate any and all help. @mathetes 

@DaisyB2015 

 

Give this a try. It seems to me it's working accurately. I did NOT, however, check all your formulas. The only ones I modified are those in columns K, L, M, N.

 

Let me know either way...if it doesn't work, let me know what's happening that should not be.

Thank you so much. This works out perfectly for what my work needs are. I appreciate the assists. @mathetes