1、解释：
正巧书读到这里了，就顺手做一下，因为压根是专业外的人，所以用词不够准确计算不够智能不要挑剔，我尽力了……包括ROI、PBP、NPV、IRR。
2、EXCEL公式：
| 输入区 | 结果显示区 | |||||
|---|---|---|---|---|---|---|
| 初始投资额 | 年均持续收入 | 年均支出 | 终止时收入（不计入年均收入） | 年数 | ROI（投资回报率） | PBP（投资回收期） |
| 600 | 12 | =IF(A3=””,””,(B3-C3)/A3) | =IF(F3<>””,ROUND(IF(F3=””,””,(1/F3)),2),””) | |||
| 1200 | 5 | =IF(OR(D4=””,E4=””),””,((D4-A3)/E4+(B3-C3))/A3) | =IF(F4<>””,ROUND(XLOOKUP(MIN(J4#),J4#,G4:G102)-1+ABS(XLOOKUP(XLOOKUP(MIN(J4#),J4#,G4:G102)-1,G4:G102,H4:H102))/XLOOKUP(XLOOKUP(MIN(J4#),J4#,G4:G102),G4:G102,I4:I102),2),””) |
| 输入区 | 结果显示区 | 输入区 | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 初始投资额 | 年均持续收入 | 年均支出（不计入折现率） | 折现率（小数） | 终止时收入（不计入年均收入） | 年数 | 年份拆解 | 净现金流折现计算 | NPV | PBP | 1 | 2 | 3 | 4 | 5 | ||||||
| 600 | 12 | 0.1 | 1200 | 5 | =IF(G3:G100>0,G3:G100,””) | =IFERROR(XLOOKUP(I3#,G3:G100,H3:H100),””) | =IF(A3<>””,IF(E3<>””,(J3+(B3-C3)/(1+D3)^G3)-ABS(K4),SUM(J3:$J$100)-$A$3),””) | =IF(A3<>””,XLOOKUP(MIN(L3:L100),K3:K100,I3#)-1+(ABS(XLOOKUP(XLOOKUP(MIN(L3:L100),K3:K100,I3#)-1,I3#,K3:K100)/XLOOKUP(XLOOKUP(MIN(L3:L100),K3:K100,I3#),I3#,J3#))),””) | 预计期限 | 5 | 预计折现率 | 0.1 | 0.12 | 0.16 | 0.17 | 0.18 | ||||
| =IF(ABS(SUM(J4:$J$100)-$A$3)<>$A$3,SUM(J4:$J$100)-$A$3,””) | 初始投资额 | 150 | 结果显示区 | |||||||||||||||||
| =IF(ABS(SUM(J5:$J$100)-$A$3)<>$A$3,SUM(J5:$J$100)-$A$3,””) | NPV | NPV | NPV | NPV | NPV | IRR | ||||||||||||||
| =IF(ABS(SUM(J6:$J$100)-$A$3)<>$A$3,SUM(J6:$J$100)-$A$3,””) | 预计收入 | 预计支出 | 年数 | =IFERROR(SUM(S7#)-$Q$4,””) | =IFERROR(SUM(T7#)-$Q$4,””) | =IFERROR(SUM(U7#)-$Q$4,””) | =IFERROR(SUM(V7#)-$Q$4,””) | =IFERROR(SUM(W7#)-$Q$4,””) | =XLOOKUP(MIN(IF(S6:W6>0,S6:W6)),S6:W6,S3:W3)+(MIN(IF(S6:W6>0,S6:W6))/((MIN(IF(S6:W6>0,S6:W6))-MAX(IF(S6:W6<0,S6:W6)))))*(XLOOKUP(MAX(IF(S6:W6<0,S6:W6)),S6:W6,S3:W3)-XLOOKUP(MIN(IF(S6:W6>0,S6:W6)),S6:W6,S3:W3)) | |||||||||||
| =IF(ABS(SUM(J7:$J$100)-$A$3)<>$A$3,SUM(J7:$J$100)-$A$3,””) | 20 | =Q3-Q3+1 | =IFERROR($P7:$P106/(1+S3)^$R7:$R106,””) | =IFERROR($P7:$P106/(1+T3)^$R7:$R106,””) | =IFERROR($P7:$P106/(1+U3)^$R7:$R106,””) | =IFERROR($P7:$P106/(1+V3)^$R7:$R106,””) | =IFERROR($P7:$P106/(1+W3)^$R7:$R106,””) | |||||||||||||
| =IF(ABS(SUM(J8:$J$100)-$A$3)<>$A$3,SUM(J8:$J$100)-$A$3,””) | 20 | =IF(R7<>””,IF(R7+1<=$Q$3,R7+1,””),””) | ||||||||||||||||||
| =IF(ABS(SUM(J9:$J$100)-$A$3)<>$A$3,SUM(J9:$J$100)-$A$3,””) | 20 | =IF(R8<>””,IF(R8+1<=$Q$3,R8+1,””),””) | ||||||||||||||||||
| =IF(ABS(SUM(J10:$J$100)-$A$3)<>$A$3,SUM(J10:$J$100)-$A$3,””) | 20 | =IF(R9<>””,IF(R9+1<=$Q$3,R9+1,””),””) | ||||||||||||||||||
| =IF(ABS(SUM(J11:$J$100)-$A$3)<>$A$3,SUM(J11:$J$100)-$A$3,””) | 200 | =IF(R10<>””,IF(R10+1<=$Q$3,R10+1,””),””) |
3、Excel源文件：
若有收获，就点个赞吧
0 人点赞
上一篇:财务评估指标、活动历时估算
下一篇:一点EXCEL的技巧（一）
