在現代財務管理中，利用 Excel 進行貸款與投資計算已成為一項必備技能。無論你是計算房貸、車貸，還是進行投資評估，Excel 提供了豐富的財務函數，能夠快速算出每期還款金額、分解利息與本金，甚至計算複雜的有效利率與未來價值。本文將從基本概念出發，逐步介紹各類財務函數及其應用，讓你輕鬆建立完整的還款攤還表與投資模型。
一、等額本息攤還法基本概念
等額本息攤還法是指在約定的貸款期限內，每期支付固定金額，其中包含利息和本金。初期還款中利息所佔比例較大，隨著本金逐步償還，後期支付的本金比例逐漸上升。這種方法便於借款人制定穩定的預算，也方便貸款機構進行風險評估。
二、Excel 中的核心財務函數
Excel 為我們提供了多個針對借款與投資的財務函數，下面將依功能分類說明：
1. 還款金額與分解計算
PMT 函數
功能：計算每期固定還款金額（本金＋利息）。
語法：
=PMT(rate, nper, pv, [fv], [type])- rate：每期利率（例如年利率需除以 12 得月利率）。
- nper：總付款期數。
- pv：現值（貸款本金，通常以負值表示現金流出）。
- fv：未來值（預設為 0，表示貸款還清後無餘額）。
- type：付款時點（0 表示期末，1 表示期初）。
例子：貸款 300,000 元、年利率 6.5%、貸款 24 期（按月還款）：
=PMT(6.5%/12, 24, -300000)IPMT 函數
功能：計算指定期次中支付金額中的利息部分。
語法：
=IPMT(rate, per, nper, pv, [fv], [type])- per：指定的期數（介於 1 與 nper 之間）。
例子：計算第 4 期的利息：
=IPMT(6.5%/12, 4, 24, -300000)PPMT 函數
功能：計算指定期次中支付金額中的本金部分。
語法：
=PPMT(rate, per, nper, pv, [fv], [type])例子：計算第 4 期的本金：
=PPMT(6.5%/12, 4, 24, -300000)CUMIPMT 函數
功能：計算從某一期到某一期之間累計支付的利息。
語法：
=CUMIPMT(rate, nper, pv, start_period, end_period, type)例子：計算第 2 期至第 6 期的累計利息。
CUMPRINC 函數
功能：計算從某一期到某一期之間累計償還的本金。
語法：
=CUMPRINC(rate, nper, pv, start_period, end_period, type)例子：計算第 2 期至第 6 期累計償還的本金。
2. 有效與名目利率的計算
EFFECT 函數
功能：根據名目年利率與每年複利計算次數，計算出實際的有效年利率（實質年利率），反映真實的資金成本。
語法：
=EFFECT(nominal_rate, npery)- nominal_rate：名目年利率（例如 6%）。
- npery：每年複利計算次數（例如 12 表示每月複利）。
例子：計算出的有效年利率會高於 6%，反映出複利效應。
=EFFECT(6%, 12)NOMINAL 函數
功能：根據有效年利率與每年複利計算次數，計算出名目年利率。
語法：
=NOMINAL(effect_rate, npery)例子：若已知有效年利率，透過此函數可反推名目利率。
3. 未來價值與變動利率的計算
FVSCHEDULE 函數
功能：根據初始本金與一系列不同期間的複利利率，計算出未來值，適用於利率隨時間變動的情況。
語法：
=FVSCHEDULE(principal, schedule)- principal：初始本金。
- schedule：一個陣列或儲存格範圍，包含各期的複利利率。
例子：若投資 100,000 元，並有一系列逐期變化的利率，使用 FVSCHEDULE 可計算出最終未來值。
三、建立貸款攤還表
製作貸款攤還表可以直觀展示每期還款中的利息、本金及剩餘本金。基本步驟如下：
- 輸入基本資料
在工作表中輸入：- 貸款本金（例如 300,000 元）
- 年利率（例如 6.5%）
- 還款期數（例如 24 期）
- 計算月利率與每月還款金額
- 月利率：
=年利率/12 - 每月還款金額：使用 PMT 函數
=PMT(6.5%/12, 24, -300000)
- 月利率：
- 建立表頭
包括：期數、還款金額、利息、本金、剩餘本金。 - 利用 IPMT 與 PPMT 分解還款
- 在每期計算利息：
=IPMT(6.5%/12, 期數, 24, -300000) - 計算本金：
=PPMT(6.5%/12, 期數, 24, -300000) - 剩餘本金：上期剩餘本金 + 當期本金（注意本金通常返回負值，符號需調整）。
- 在每期計算利息：
- 利用 CUMIPMT 與 CUMPRINC 驗證
可使用累計函數核對某段期間內的總利息與本金是否合理，確保攤還表計算正確。
四、實際應用案例
以一筆 300,000 元、年利率 6.5%、貸款期數 24 期的例子來說明：
- 每月還款金額：
=PMT(6.5%/12, 24, -300000)得到約 13,364 元（注意結果為負值，代表現金流出）。 - 第 1 期計算：
- 利息：300,000 × (6.5%/12) ≈ 1,625 元
- 本金：13,364 − 1,625 ≈ 11,739 元
- 剩餘本金：300,000 − 11,739 ≈ 288,261 元
- 利用 CUMIPMT 與 CUMPRINC 計算累計值：
- 累計利息（第 1 至 24 期）：
=CUMIPMT(6.5%/12, 24, 300000, 1, 24, 0) - 累計本金：
=CUMPRINC(6.5%/12, 24, 300000, 1, 24, 0)
- 累計利息（第 1 至 24 期）：
- 變動利率投資的 FVSCHEDULE：
如果你的投資利率並非固定，而是隨時間變化，例如一系列利率 {5%, 5.5%, 6%, …}，則可用 FVSCHEDULE 來計算最終未來值=FVSCHEDULE(Initial_Principal, {0.05, 0.055, 0.06, ...}) - 名目與實質利率轉換：
假設銀行提供的名目年利率為 6%，若每年複利 12 次，可用 EFFECT 求得實質年利率：=EFFECT(6%, 12)反過來，用 NOMINAL 可從已知的實質年利率反推出名目年利率：=NOMINAL(有效年利率, 12)
建立動態的貸款攤還表與投資模型不僅可以幫助個人更好地規劃財務，也可用於企業決策中評估不同融資方案。務必注意各參數單位的一致性以及現金流方向的正負號問題，以確保計算準確無誤。